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.
=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.
Accepted Solutions
=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.