Code:

`https://exceljet.net/formula/index-and-match-with-multiple-criteria`

it is possible to enter a non-array formula to replicate an Index Match with multiple criteria.

The formula is:

Code:

`=INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))`

and it states:

Code:

`The INDEX function can handle arrays natively, so the second INDEX is added only to "catch" the array created with the boolean logic operation and return the same array again to MATCH. To do this, INDEX is configured with zero rows and one column. The zero row trick causes INDEX to return column 1 from the array (which is already one column anyway).`

When I apply it to the data:

Code:

`=INDEX(E5:E11,MATCH(1,INDEX((H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0,1),0))`

I do get the correct answer.

What I don't understand is when I evaluated this part of the formula:

Code:

`INDEX((H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0,1)`

I got a value of 0, so I tried this:

Code:

`=INDEX(E5:E11,MATCH(1,0,0))`

but it returned a value of #N/A.

Why couldn't I replace the Index part with a 0 to get the correct result?

Thanks