Matrix lookup with multiple criteria

[2005natsfan]

I've been trying to write a function to solve this issue: the answers to two questions gives you a third answer based on a matrix with multiple criteria.  I've been able to solve within Google Sheets and Excel using Index, Match, Match however this does not seem to work in AppSheet.  

Here is my matrix of possible answers:

ID	FNWS	NWA1	NWA2	NWA3	NWA4	NWA5
1	x	None	1-10 DB	1-10	1-30	1-100
2	None	0	0	0	0	0
3	1-10	1	2	3	3	3
4	1-30	1	2	3	4	4
5	1-100	1	2	3	4	5

I've been at this for a few days now so any help would be appreciated.  Thank You!

[TeeSee1]

I do not think it really is a good 'table' (this actually does not have a key...) to work with but if you must...

SWITCH(
 [NWA],
 "None", ANY(SELECT(table[NWA1],[FNWS] = [_THISROW].[FNWS]),
 "1-10 DB", ANY(SELECT(table[NWA2],[FNWS] = [_THISROW].[FNWS]),
 and so on...
)

This assumes that you have another table with fields [NWA] and [FNWS] that provide search values. (if I understand your requirements correctly...)

I would instead create a table with

• [id], [FNWS], [NWA], [SCORE]

Then the expression would be

INDEX(
 SELECT(
  SCORES[SCORE],
  AND(
   [FNWS] = [_THISROW].[FNWS],
   [NWA] = [_THISROW].[NWA]
  )
 ),
 1
)

[2005natsfan]

Thank you for your reply.  I tried the first formula you suggested and I got an error message.  Here is the full formula, I could have made a mistake.

SWITCH(
[NWA],
"None", ANY(SELECT(table[NWA1],[FNWS] = [_THISROW].[FNWS]),
"1-10 DB", ANY(SELECT(table[NWA2],[FNWS] = [_THISROW].[FNWS]),
"1-10", ANY(SELECT(table[NWA2],[FNWS] = [_THISROW].[FNWS]),
"1-30", ANY(SELECT(table[NWA2],[FNWS] = [_THISROW].[FNWS]),
"1-100", ANY(SELECT(table[NWA2],[FNWS] = [_THISROW].[FNWS]),
)

I also wanted to explain more about my table and what I am trying to do.  I am trying to build a data collection form.  Most of the field are EnumList.  Some of the fields we need to be dependent on answer from other fields based on certain rubric/tables like the one below.  For example, if I choose 1-10DB for NWA and for FNWS I choose 1-30, the answer for FWWS Rubric Score would be 2.  Does this make sense?

ID	FNWS	NWA1	NWA2	NWA3	NWA4	NWA5
1	x	None	1-10 DB	1-10	1-30	1-100
2	None	0	0	0	0	0
3	1-10	1	2	3	3	3
4	1-30	1	2	3	4	4
5	1-100	1	2	3	4	5

Thank you again for you help with this.  Please let me know what questions this generates.

[TeeSee1]

"None", ANY(SELECT(table[NWA1],[FNWS] = [_THISROW].[FNWS])),

The closing ) is missing. Also "table" should be replaced by the actual table name.