Table Lookup - Multiple Criteria

Hi !

I'm hoping someone can point me in the right direction!

I have a table of values (Please see attachment) which I need to query, then return a result based matching a range, as well as meeting one other criteria.

Units 1 2 3 4 5
20-49 3.67 4.43 5.18 5.99 6.80
50-99 2.16 2.48 2.92 3.35 4.05

User needs to enter a unit qty: 55
User needs to enter a number qty (EG:1,2,3,4 or 5): 3

Formula needs to return the price/value 2.92

The formula will need to determine that the unit qty entered sits within the range (50-99), then will need to display the price/value based the number qty entered (3).

Thus, displaying: 2.92 !

Simple for you I'm sure.... bugging the sh|t out of me! Any assistance would be greatly appreciated.

Thanks!JDW-TableLookup.xlsx

Hi Jdw,

Welcome to the forum.

Try using below array formula:-

{=INDEX($A$1:$O$11,MIN(IF($A$3:$A$11>=$D$14,ROW($A$3:$A$11),""),IF($B$3:$B$11>=$D$14,ROW($B$3:$B$11),"")),MATCH($D$15,1:1,0))}

See attached:- JDW-TableLookup.xlsx


Regards,
DILIPandey

<click on below 'star' if this helps>

maybe also like this???

=INDEX(D3:O11,IFERROR(MATCH(D14,C3:C11,1),0)+1,MATCH("Print No - "&D15,D2:O2,0))

with a helper column.

JDW-TableLookup.xlsx

Your welcome from : dilip and vlady..

cheers

Thanks.. I like this vlady

Regards,
DILIPandey

<click on below 'star' if this helps>