Index match multiple criteria - can't understand why it doesn't work!

[Ronnie12345]

Hi everyone,

You would be doing me a big favour if you would explain why the following produces a #REF ! error.

The formula is simply trying to find a match of Harry and Stan to return the number 13.
[TABLE="width: 448"]
<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][TABLE="width: 384"]
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]Q[/TD]
[TD="width: 64"]R[/TD]
[TD="width: 64"]S[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Fred[/TD]
[TD]Smith[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]John[/TD]
[TD]Jones[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Peter[/TD]
[TD]Blythe[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Harry[/TD]
[TD]Stan[/TD]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]Harry[/TD]
[TD]Stan[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: center"]#REF ![/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="colspan: 5"]INDEX(S2:S5,MATCH(Q8,Q2:Q5,0),MATCH(R8,R2:R5))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
</tbody>[/TABLE]

 

[FormR]

You would be doing me a big favour if you would explain why the following produces a #REF ! error.

Hi, the second parameter of the INDEX() function is the column that you want to return, so you are saying give me the value in the 4th row of the 4th column of the range S2:S5 - but there is only one column, hence the REF! error.

You could try this instead:

=INDEX(S2:S5,MATCH(1,INDEX((Q2:Q5=Q8)*(R2:R5=R8),0),0))

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=INDEX(S2:S5,MATCH(Q8,IF(R2:R5=R8,Q2:Q5),0))

 

[Trevor G]

Another one use Ctrl + Shift + Enter as it is an Array formula

=INDEX(S2:S5,MATCH(Q8&R8, Q2:Q5&R2:R5, 0))