Lookup a value within a range with 2 additional criteria, return a value

I have two work sheets. The Lookup worksheet has 3 pieces of entered data: Number, Amount, and UOM. The Data worksheet contains the same data except the Amount is listed as a range (AmountMin and AmountMax), and a corresponding price. I need a formula in the Lookup worksheet (Price column) to match the Number and UOM together with the Number and UOM in the Data worksheet, and return a price based on the Amount value in the Lookup worksheet falling within the AmountMin and AmountMax values in the Data worksheet. Looked around for a solution and found some for the lookup and value within a range applications separately, but having a hard time combining the two processes. Thank you in advanced. (Example book attached).

You could use this array* formula in D2 of the lookup sheet:

=IFERROR(INDEX(DATA!E:E,MATCH(1,(A2=DATA!$A$2:$A$10)*(C2=DATA!$B$2:$B$10)*(B2>=DATA!$C$2:$C$10)*(B2<=DATA!$D$2:$D$10),0)+1),"")

*Note that an array formula needs to be committed using the key combination of Ctrl-Shift-Enter, instead of the usual < Enter >.

Then you can copy it down as required.

An alternative would be to use SUMIFS, like this:

=SUMIFS(DATA!E:E,DATA!A:A,A2,DATA!B:B,C2,DATA!C:C,"<="&B2,DATA!D:D,">="&B2)

which does not need to be array-entered (and is shorter to type, AND can accept full-column references without a time penalty)

Hope this helps.

Pete

Awesome! And if I wanted to return different data based on the same criteria (Number, Amount and UOM), would I only have to change the first parameter? Such as in the attached. I went with the SUMIFS solution above.

I can't see your formula in the workbook you attached, but, yes, the first parameter in SUMIFS defines the range where you want to return data from. The other parameters relate to the criteria to be applied, and are in pairs, the first of which is the range that the criteria applies to and the second is the criteria itself.

Thanks for the rep, but could you just do another bit of "admin" and select Thread Tools from the menu above and to the right of your first post in this thread, and mark this thread as SOLVED.

Pete