Two criteria lookup - exact & closest match

**kapucino** · 06-03-2010, 10:56 PM

I checked previous post on rather popular question on two criteria lookups. Could not find hints on two criteria lookups where one criteria requires exact match (vlookup false) and second criteria requires closest match (true in vlookup). Any ideas?

see example

**Paul** · 06-03-2010, 11:32 PM

Hi Kapucino, welcome to the forum.

Perhaps this array formula will work for you (confirmed with CTRL+SHIFT+ENTER, not just ENTER):

=INDEX(D2:D19,MATCH(G3&H3,A2:A19&B2:B19,1))

**kapucino** · 06-04-2010, 09:03 AM

Originally Posted by Paul

Hi Kapucino, welcome to the forum.

Perhaps this array formula will work for you (confirmed with CTRL+SHIFT+ENTER, not just ENTER):

=INDEX(D2:D19,MATCH(G3&H3,A2:A19&B2:B19,1))

Man.. Works like a charm on a sample.

Must crack&understand the code and use it on more complex case .. Used MATCH before but not in this manner.

THANKS a LOT!

I see i must not forget that search area must be in asceding order because of 1 in Match! Suprised that match can do closest match on caombination of text&value

**SPAMACCOUNT** · 01-28-2012, 01:19 PM

Thanks for this great post! I've just tried this array formula it and can't get it to work on my particular use case though. Work fine in original post.
Is it to do with spaces or repeating return value in my example attached?

Test Double Closest.xls

Thank you so much!