INDEX/MATCH multiple criteria

1
votes

I am making up a tab with data ordered vertical, and need to index and match with another tab where the data is ordered horizontal.

I have tried to do a tutorial on this, and it seemed do-able. I made an array formula which I think captures all criteria to get the right value, but it gives me #N/A.

I figured I'd use the following three criteria:

  • Row name (in this case "XXX")
  • Family name (in this case "Amphipoda")
  • "Count", as that's the exact row I want to get a result from.

The formula is:

{=INDEX(Family_Distribution!A1:Z271,MATCH(1,(Family_Distribution!C1:X1=all_Results_Habitats!A38)*(Family_Distribution!A1:A271=all_Results_Habitats!B1)*(Family_Distribution!B:B="Count"),0),3)}

The table to perform the indexing and matching with looks like this:

Table to index and match with

The table where I place the formula looks like this:

Table to place the formula in

What am I doing wrong?

2
arraysexcelindexingmatch
Have you tried stepping through the formula with the "Evaluate Formula" on the "Formulas" tab? - Mistella

2 Answers

0
votes

Give this a try, no array entry needed:

=INDEX(Family_Distribution!$C$2:$Z$271,MATCH(1,INDEX((Family_Distribution!$A$2:$A$271=all_Results_Habitats!B1)*(Family_Distribution!$B$2:$B$271="Count"),),0),MATCH(all_Results_Habitats!A38,Family_Distribution!$C$1:$Z$1,0))
0
votes

I am not going to retype all of that to test, so this is untested:

=SUMIFS(INDEX(Family_Distribution!A:Z,MATCH($B$1,Family_Distribution!A:A,0)+MATCH("Count",Family_Distribution!B:B,0)-1,0),INDEX(Family_Distribution!A:Z,MATCH($B$1,Family_Distribution!A:A,0),0),B38)