Exceljet

Quick, clean, and to the point

How to troubleshoot VLOOKUP approximate match

When you're using VLOOKUP for approximate matches, you're not as likely to run into the problem of values not being found. However, you still need to understand how approximate matches work.

Here's the commission example we looked at earlier, which uses VLOOKUP to find approximate matches in a named range called commission table. Let's look at how VLOOKUP handles approximate matches in more detail.

First, when you're using VLOOKUP for approximate matches it's likely that the lookup value won't be in the table. That's why you're using the approximate match setting to start with.

In that case, Excel moves through the lookup values in the table until it reaches a value that's greater than the lookup value. Then it steps back one row and returns the result from the column you've specified.

If the lookup value has a perfect match in the lookup table , you'll get the value from the matched row, just like you would using VLOOKUP in exact match mode. For example, if I change Chung's sales figure to 100,000 even, VLOOKUP will return a commission rate of 5%.

What if the lookup value is greater than the top value in the table? In that case, VLOOKUP will return a value from the last value in the table. This is what we see with Tanaka. There is no sales number in the table greater than 200,000, so VLOOKUP returns the value in the last row, which is 9%.

Finally, what happens if the lookup value is less than then first value in the table? In that case, you'll see an NA error. For example, if I change Bueller's sales figure to $49,000. VLOOKUP returns NA.

To handle this situation, you could use IFERROR to catch the NA error and return a commission rate.

However, a better solution is just to add another row to the table to handle lower sales numbers. So I'll do that instead.

Note If I insert a new first row above the existing first row, the new row won't be included in the named range commission table. I'll undo that.

So, instead  I'll insert a new second row and fill down the values from the first row. Then I'll change the first row so that it handles all commissions down to zero.

Now our named range includes all rows in the table, and VLOOKUP correctly calculates a commission rate for Bueller.

Course 
Core Formula

Related shortcuts

Author 
Dave Bruns