Hi - Dave here.
Happy Friday!
An interesting problem in Excel is how to calculate quantity-based discounts. In the worksheet below, the goal is to calculate a discount for each item based on quantity using the discounts in the table to the right.
The purpose of the table is to allow each item to have its own set of discounts. Notice that Donuts have a different discount for a quantity of 24. The discounts for other items can be customized as well.
This is a classic two-way lookup problem. The formula must perform an exact match on the item name and an approximate match on the quantity. The formula in E5 is:
=XLOOKUP(B5,item,XLOOKUP(C5,quantity,discount,0,-1),0)
Where
item,
quantity, and
discount are named ranges as shown:
[
Click here for all the details]
Notice XLOOKUP is used twice in the formula above. Once for the quantity, and once for the item. This works very nicely, but it can be confusing to have one XLOOKUP inside another. Another way to solve this problem is with INDEX and MATCH, which will be more intuitive for many users. The article above explains both formulas in detail. Download the workbook and give it a try.
Excel formulas
The only way to be good with Excel formulas is to
practice.
We maintain a list of over 500 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
PS - Just a reminder that if you want to learn how to automate tasks in Excel with VBA and Macros,
Victor's course is on sale until next Wednesday, May 24.
