Quantity based discount

The goal is to calculate discounts on a per-item and per-quantity basis using the discount table at the right in the workbook shown. 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 page for a very simple quantity-based discount.

Useful links

• How to use the XLOOKUP function
• How to use INDEX and MATCH

XLOOKUP solution

One way to solve this problem is with the XLOOKUP function. XLOOKUP is a modern and flexible replacement for older functions like VLOOKUP, and HLOOKUP. The generic syntax for XLOOKUP is:

=XLOOKUP(lookup,lookup_array,return_array,[not_found],[match_mode],[search_mode])

In this problem, the trick is to use XLOOKUP twice: once to retrieve discount information based on the quantity and once to retrieve discount information based on the item. The screen below shows the two lookups that are required:

Let's start with quantity, which is lookup #1 in the screen above. The goal is to retrieve the correct set of discounts for the quantity in column C. To do this, we use XLOOKUP like this:

XLOOKUP(C5,quantity,discount,0,-1)

The lookup_value comes from cell C5. The lookup_array is the named range quantity (I4:L4). For return_array, we provide the named range discount (I5:L11). Because we want a discount of 0% for items that do not appear in the discount table, we provide zero (0) for the if_not_found argument. Finally, because the quantity lookup is not an exact match, we provide -1 for match_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 for lookup_array and that the return_array can be a two-dimensional range containing all discount values. This means that XLOOKUP will return an entire column of discounts after matching a given quantity. For example, with the number 12 in cell C5, XLOOKUP returns all discounts in column K in an array like this:

{0.1;0.1;0.1;0.1;0.1;0.1;0.1}

These decimal values correspond to the percentages seen in the range K5:K11. Because we set match_mode to -1, XLOOKUP will continue to return the same set of discounts until the quantity reaches 24. So, at this point, we have 7 discount values, but we still need to select the correct discount based on the item name. This is lookup #2 in the screen above.

Note: It happens that all discounts for a quantity of 12 are 10%, but the table is structured to allow the discounts to be customized on a per-item basis and the formula will continue to work correctly.

To retrieve the correct discount for an item, we start off like this:

=XLOOKUP(B5,item,

The lookup_value comes from cell B5 and the lookup_array is the named range item (H5:H11). Now we need to add the return_array. This is where things get tricky. If we provide the named range discount, we'll get back all discounts in the matching row. For Lemons, the result looks like this:

=XLOOKUP(B5,item,discount) // returns {0,0.05,0.1,0.15}

However, we don't want all of the discounts for Lemons. Instead, we want the single discount associated with a quantity of 12. So, in order to get that result, we provide lookup formula #1 as the return_array:

=XLOOKUP(B5,item,XLOOKUP(C5,quantity,discount,0,-1)) // returns 0.1

Recall that the result from lookup #1 is the entire column of discounts 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.

We are nearly finished, but we have one more requirement: when an item is not found in the discount table, we should assume that there is no discount for that item. In other words, we should assume a discount of 0%. To accomplish this in XLOOKUP, we can provide zero for the if_not_found argument. The final formula in cell E5 is:

=XLOOKUP(B5,item,XLOOKUP(C5,quantity,discount,0,-1),0)

As the formula is copied down, it uses the discount table to find the right discount for each item and quantity. When no discount is available for a given item, it returns zero (0).

Applying the discount

The formula in column F calculates the total price for each item and applies the discount in column E with this formula:

=C5*D5*(1-E5) // apply discount

When the discount is zero, the quantity x price calculation is not affected.

INDEX and MATCH option

This problem can also be solved nicely with an INDEX and MATCH formula like this:

=INDEX(discount,MATCH(B5,item,0),MATCH(C5,quantity,1))

This traditional two-way lookup formula is an example of how the INDEX and MATCH can sometimes be more intuitive than using XLOOKUP twice. In a nutshell, we feed the named range discount into the INDEX function as the array argument. For row_num, we use the MATCH function for an exact match on item:

MATCH(B5,item,0) // get row num

 For column_num, we use the MATCH function for an approximate match on quantity:

MATCH(C5,quantity,1) // get column num

After the MATCH function is evaluated, we have 5 for row, and 3 for column, and INDEX returns the value at that location: 

INDEX(discount,5,3) // returns 0.1

The result is 0.1, which will display as 10% when formatted as a percentage. This is the correct discount for 12 Lemons. Note that if the item is not found in the discount table, MATCH will return #N/A, and INDEX will return the same. To handle the case where the item is not found in the discount table (and therefore the discount should be 0%), we wrap the entire INDEX and MATCH formula inside the IFNA function, and provide zero for value_if_na like this:

=IFNA(INDEX(discount,MATCH(B5,item,0),MATCH(C5,quantity,1)),0)

We could use the IFERROR function instead of IFNA, but IFNA is more specific and therefore a safer option in this case, since it will only trap #N/A errors. For more information about XLOOKUP versus INDEX and MATCH, see this article.

VLOOKUP + MATCH option

Since all discounts are in a single table with the lookup value (Item) in the first column, it is possible to use VLOOKUP + MATCH to solve this problem as well. The trick is to use the MATCH function to locate the right column number to return to VLOOKUP:

=IFNA(VLOOKUP(B5,$H$5:$L$11,MATCH(C5,$H$4:$L$4,1),0),0)

Because VLOOKUP requires the lookup values to be part of the table, and the quantities in row 4 start in the next column, we can't use the named ranges mentioned above directly, so this formula uses absolute references instead. See a more detailed example of a VLOOKUP + MATCH formula here.