Index/Match with mutiple criteria and match type 1

[raven_squire]

Hello,

I am trying to index/match multiple criteria. The formula below meets most of my needs but I need the match type to be 1 not zero.
=INDEX(E23:E5000,MATCH(A3&B3&F3&C3,A23:A5000&B23:B5000&F23:F5000&C23:C5000,0))

All the criteria will match in their respective columns exactly, except for C3 witch is => column c and =< column d. So I can get away with a match type of 1 and just compare to column C. However when I change the match type to 1 with this formula it returns "0" and -1 returns "#N/A". Zero is not contained within the data so I am not sure what is happening.

Interestingly this formula works identically and has the same errors

=INDEX(E23:E5000,MATCH(A3&B3&F3&C3,A23:A5000&B23:B5000&F23:F5000&C23:C5000,0&0&0&0))

There appears to be little information available on the net about Index/Match with multiple criteria so you help will be greatly appreciated.

 

[bebo021999]

How about:
=LOOKUP(2,1/((A23:A5000=A3)*(B23:B5000=B3)*(F23:F5000=F3)*(C23:C5000<=C3)*(D23:D5000>=C3)),E23:E5000)

 

[raven_squire]

Hey, that works. I will have to figure out how it works now. Thank you!!

 

[raven_squire]

I just read up about lookup and looks like you have some white magic happening there.
The syntax for lookup as far as I can see is LOOKUP(lookup_value, lookup_vector, [result_vector])

Why does the formula look up "2"?
How does 1/1/((A23:A5000=A3)*(B23:B5000=B3)*(F23:F5000=F3)*(C23:C5000<=C3)*(D23:D5000>=C3)) refer to a vector?

I am happy to have the problem solved but it appears that there is a lot to learn about the answer.

 

[bebo021999]

For example, criteria statement, A23:A5000=A3 establish range of TRUE/FALSE.
But this lookup vector ,{FALSE,TRUE,FALSE,...}, may be not in increasing order as being required from lookup function.
So try to convert it to: 1/(A23:A5000) = {#DIV/0,1,#DIV/0,...}
Then LOOKUP value 2 (or any value that greater than 1) to value 1 in lookup vector.

Hope this helps.

 

[raven_squire]

That helps a lot, I also found this article that explains it (it is towards the bottom of the article). Thanks
https://blogs.office.com/2012/04/26/using-multiple-criteria-in-excel-lookup-formulas/

 

[Aladin Akyurek]

I just read up about lookup and looks like you have some white magic happening there.
The syntax for lookup as far as I can see is LOOKUP(lookup_value, lookup_vector, [result_vector])

Why does the formula look up "2"?
How does 1/1/((A23:A5000=A3)*(B23:B5000=B3)*(F23:F5000=F3)*(C23:C5000<=C3)*(D23:D5000>=C3)) refer to a vector?

I am happy to have the problem solved but it appears that there is a lot to learn about the answer.

That helps a lot, I also found this article that explains it (it is towards the bottom of the article). Thanks
https://blogs.office.com/2012/04/26/using-multiple-criteria-in-excel-lookup-formulas/

See:
http://www.mrexcel.com/forum/excel-questions/99621-lookup-value-unsorted-data.html