tiredofit
Well-known Member
- Joined
- Apr 11, 2013
- Messages
- 1,825
- Office Version
- 365
- 2019
- Platform
- Windows
According to this article:
it is possible to enter a non-array formula to replicate an Index Match with multiple criteria.
The formula is:
and it states:
When I apply it to the data:
I do get the correct answer.
What I don't understand is when I evaluated this part of the formula:
I got a value of 0, so I tried this:
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
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