I am trying to perform a Lookup of a Name and Value against a Lookup List which contains the Name, UpperBound, LowerBound and Price. I want to return the price if it falls under the range, for example:
Sheet1:
| Name | Quantity | Price |
|---|---|---|
| Apple | 5 | ?? |
| Apple | 11 | ?? |
| Apple | 23 | ?? |
| Grape | 9 | ?? |
| Grape | 27 | ?? |
| Grape | 40 | ?? |
I want to get the price above from the following list.
Sheet 2:
| Name | Lower Bound | Upper Bound | Price |
|---|---|---|---|
| Apple | 0 | 10 | $10.00 |
| Apple | 11 | 20 | $29.50 |
| Apple | 21 | 30 | $28.20 |
| Grape | 0 | 15 | $22.10 |
| Grape | 16 | 24 | $31.20 |
| Grape | 25 | 50 | $35.20 |
Please note that the above is an example and in the actual sheets I have 1000s of Products and attributes.
Yet another way:
=FILTER($D$2:$D$7,($A$2:$A$7=G2)*($C$2:$C$7>=H2)*($B$2:$B$7<=H2))
You can use VLOOKUP with last parameter omitted
=MAP(A2:A7,B2:B7,LAMBDA(name,qty,VLOOKUP(qty,FILTER(B11:D16,A11:A16=name),3)))
Try using the following formula :
=LET(x, FILTER(G$2:I$4,$A2=$F$2:$F$4), VLOOKUP($B2,x,3,1))
You can use this formula:
=SUMPRODUCT(tblConfig[Price]*
(tblConfig[Name]=[@Name])*
(tblConfig[Upper Bound]>=[@Quantity])*
(tblConfig[Lower Bound]<=[@Quantity]))
I named the table for the price ranges tblConfig
