How can I LOOKUP a Value with Conditions Bound by a Range

New Contributor

I have Sheet A - Price List that lists multiple prices (Column D) per Product ID depending on the Quantity that is purchased (range from and to in Columns B and C).  In Sheet B - Lookup Price Based on Product ID AND Quantity, I would like to lookup the specific price per product based on the Product ID in Column G AND Quantity in Column H (  None of the lookup functions (XLOOKUP, VLOOKUP) work as they are not capable of multiple conditions and just pull the first price entry for Product ID.  I was exploring FILTER but also could not get that to work.  Would greatly appreciate if someone could provide a formula to achieve this with a formula.image.png


2 Replies
best response confirmed by mijatoke (New Contributor)



You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021.

index match.JPG


Excellent, thank you so much for the solution!  Much appreciated