Exceljet

Quick, clean, and to the point

Basic XLOOKUP approximate match

In this video, we’ll set up the XLOOKUP function to perform an approximate match.

In this worksheet, the table in B5:C9 contains quantity-based discounts. As the quantity increases, the discount also increases.

Let's set up XLOOKUP to calculate the correct discount for each quantity shown in E5:E11.

Now, to make entering the formula a bit easier, I'm going to start by creating two named ranges

I'll name B5:B9 "quantity" and I'll name C5:C9 "discount". 

This is an optional step, but it will eliminate the need to use absolute references inside XLOOKUP. An Excel Table would also work well in this case.

Next, I'll put the cursor F5. After typing an equals sign and the letters "xl" I'll press TAB to complete.

The first argument for XLOOKUP is lookup value. We want to pick this value up from cell E5.

The next argument is lookup array. For this, we want to use the named range "quantity" in column B.

Next, we have return array. Here, we want to use the named range "discount" in column C.

Now, these are the only required arguments for XLOOKUP. If I stop here and copy the formula down, we get mostly #NA errors. This is because XLOOKUP performs an exact match by default.

XLOOKUP returns 25% for 100, because 100 appears in the lookup array, so it's an exact match. The other values do not appear in the lookup array.

What we need in this case is an approximate match, and for that we need to enter more arguments.

The next argument is the not found message. I'm going to leave this completely empty in this case, because we don't need it for this example.

Next, we have match mode. This is the argument we need for approximate match.

You can see we have two basic options:

  1. Exact match or next smaller item and
  2. Exact match or next larger item

In this case, we want next smaller, so I'll enter -1.

We don't need to provide search mode, since XLOOKUP will start at the top by default.

Now when I enter the formula, you can see we have more sensible results. For each quantity, we get the correct discount.

As before, 100 is an exact match and returns 25%.

With a quantity of 350, XLOOKUP scans forward then drops back to the next smaller item, 300, and returns 35%.

Dynamic Array Formulas are available in Excel 365 only.

Related shortcuts

Author 
Dave Bruns