SOLVED

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

Copper Contributor

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

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.

2 Replies
best response confirmed by mijatoke (Copper Contributor)
Solution

# Re: How can I LOOKUP a Value with Conditions Bound by a Range

``=INDEX(\$D\$3:\$D\$22,MATCH(1,(\$A\$3:\$A\$22=G3)*(\$B\$3:\$B\$22<=H3)*(\$C\$3:\$C\$22>=H3),0))``

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

# Re: How can I LOOKUP a Value with Conditions Bound by a Range

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

1 best response

Accepted Solutions
best response confirmed by mijatoke (Copper Contributor)
Solution

# Re: How can I LOOKUP a Value with Conditions Bound by a Range

``=INDEX(\$D\$3:\$D\$22,MATCH(1,(\$A\$3:\$A\$22=G3)*(\$B\$3:\$B\$22<=H3)*(\$C\$3:\$C\$22>=H3),0))``

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