CODEWITHSUNDEEP
excelexcel-formulamatchvlookuplookup
Maxwell
Maxwell

Reputation: 109

Lookup based on two criteria including between ranges fields

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

Upvotes: 2

Views: 55

Answers (1)

JvdV
JvdV

Reputation: 75870

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)))

Upvotes: 1

Related Questions