I’m trying to create a formula that returns the correct price from a list of price breaks. The lookup function needs to match both the part number and the order quantity. However, the order quantity could be in between two prices. In that case, it should return the lower price.

The formula I’m using is =XLOOKUP(A3&B3,$F$3:$F$20&$G$3:$G$20,$H$3:$H$20,"",-1). This is how it breaks down:

A3&B3 – I’m looking up both the value in column A and the value in column B

$F$3:$F$20&$G$3:$G$20 – The range in which to find the values from columns A and B

$H$3:$H$20 – The range of values to return if found

“” – If nothing is found, return nothing basically

-1 – This is the match mode. A -1 is described as “Exact match. If none found, return the next smaller item.”

I listed what the correct price should be based on the parameters, and about half the values returned were wrong. But not all of them.

I’m not married to the XLOOKUP. I’m just trying to find a way to get the right data.