The purpose of the discount 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.
Note that not all of the items listed in column C appear in the discount table.
This means we also need to handle the case of the item not being found (i.e.
items that do not appear in the discount table are not discounted).
This problem can be solved with XLOOKUP or INDEX and MATCH.
Both approaches are explained below.
Note: This is a more advanced example.See this pagefor a very simple quantity-based discount.
Useful links
XLOOKUP solution
One way to solve this problem is with theXLOOKUP function.
XLOOKUP is a modern and flexible replacement for older functions like VLOOKUP, and HLOOKUP.
Thelookup_arrayis the named rangequantity(I4:L4).
Forreturn_array, we provide the named rangediscount(I5:L11).
Finally, because the quantity lookup is not an exact match, we provide -1 formatch_mode.
This tells XLOOKUP to perform an exact match when available and the next smallest item if not.
Notice that XLOOKUP will happily accept a horizontal range forlookup_arrayand that thereturn_arraycan be a two-dimensional range containingall discount values.
This means that XLOOKUP will return anentire columnof discounts after matching a given quantity.
This is lookup #2 in the screen above.
Now we need to add thereturn_array.
This is where things get tricky.
If we provide the named rangediscount, we’ll get back all discounts in the matching row.
Instead, we want thesinglediscount associated with a quantity of 12.
The final result is 0.1 which, when formatted as a percentage, will display as 10%.
This is the correct discount for 12 Lemons.
In other words, we should assume a discount of 0%.
To accomplish this in XLOOKUP, we can provide zero for theif_not_foundargument.
When no discount is available for a given item, it returns zero (0).
In a nutshell, we feed the named rangediscountinto theINDEX functionas thearrayargument.
This is the correct discount for 12 Lemons.
For more information about XLOOKUP versus INDEX and MATCH,see this article.
See a more detailed example of a VLOOKUP + MATCH formulahere.
XLOOKUP supports approximate and exact matching, wildcards (* ?)
you’re free to use INDEX to retrieve individual values, or entire rows and columns.
MATCH supports approximate and exact matching, andwildcards(* ?)
