Lookup based on two criteria including between ranges fields

2
votes

I'm trying to do a Lookup formula to get get the exact value based on two fields against 3 fields (two of the lookup fields are Min and Max fields) so the idea is to get the value based on field name and another numeric field against two fields because it is a range of numbers

Lookup Table

enter image description here

Table where formula will be created (Inside LookupResult). See below the expected result where Price 150 for 101-A-A should be ABCD

enter image description here

Thanks for the help

1
excelexcel-formulamatchvlookuplookup
I hope I didn't misinterpreted your end result as it shows FDHI. Either way, sidenote: You have a ton of open questions, only one with an accepted answer. Please revisit these questions and give credit to those who helped you with your problems. Either by upvoting or accepting an answer. See the site's tour if you are unfamiliar with this site's fundamental mechanism – JvdV

1 Answers

1
votes

You just need some indexing, no need for MATCH nor VLOOKUP. For example:

enter image description here

Formula in H2:

=INDEX(D:D,MAX(INDEX((A$2:A$3=F2)*(B$2:B$3<=G2)*(C$2:C$3>=G2)*ROW(A$2:A$3),)))

Or, if rules can really only apply to a single row:

=INDEX(D:D,SUMPRODUCT((A$2:A$3=F2)*(B$2:B$3<=G2)*(C$2:C$3>=G2)*ROW(A$2:A$3)))