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 take a 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 row 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 the first value in the table? In that case, you’ll see the #N/A 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. Note that 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.” That’s because when the new row is inserted it pushes down the named range. So, instead, I’ll insert a new second row and fill down values from the first row. Then I’ll change the values in the first row so that it handles all commissions down to zero. Now the named range includes all rows in the table, and VLOOKUP correctly calculates a commission rate for Bueller.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
