Price Break Lookup Help

Administrative Note:

Is your forum profile up-to-date and showing ONLY the oldest Excel PRODUCT that you need this to work for?

Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.

The three most recent Excel products are Excel 2019, Excel 2021, Excel 2024 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.

Thanks.

In C3 and copy down,

=LOOKUP(A3 & TEXT(B3, "000"), $F$3:$F$20 & TEXT($G$3:$G$20, "000"), $H$3:$H$20)

Well that was easier than I expected. LOL....Thank you so much. That seems to work perfectly. Do you mind me asking why the quantity had to be converted to a text? Also, if there's no exact match for the quantity, how does the formula know which value to pick? I really aim to learn how a formula functions so that I can hopefully apply the knowledge in a future situation.

It works because LOOKUP returns the largest value of like type (number or text) that is not greater than the value sought. Appending the qty as text makes it sorts correctly; in Excel, "Bob2" is greater than "Bob100"

Also, you have to have a qty 1 price for every item, and the price list must be in ascending order by part number and quantity.

Ahh...makes sense now on the text. I made sure to have the data sorted correctly.

Does it return an #N/A if there is no valid price break? Say the part only has 5, 10, and 15, but I'm trying to find 3. I thought about that when I applied it to my larger file. Fortunately I don't have that problem right now, but just in case for later.

You MUST have a qty 1 price for every item so that there is a match to part number and qty; otherwise it will return the highest-qty price of the lexically lower part number.

If you have a minimum order qty, then you can instead have a price for qty 0 and mark the price as N/A.

You're welcome.