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
Last edited by kapucino; 06-04-2010 at 09:04 AM.
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.Originally Posted by Paul
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
Last edited by kapucino; 06-04-2010 at 03:02 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!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks