How to use VLOOKUP for approximate matches (video)

Abstract

In this video we look at how to configure VLOOKUP to look up values based on an approximate match. This is good for tax rates, postage, commissions, and the like.

Transcript

In a lot of cases, you'll use VLOOKUP to find exact matches based on some kind of unique id, but there are many situations where you'll want to use VLOOKUP to find non-exact matches. A classic case is using VLOOKUP to find a commission rate based on a sales number.

Let's take a look.

Here we have one table that lists sales by salesperson, and another that shows the commission that should be earned based on the sales amount.

Let's add a VLOOKUP formula in column D to calculate the appropriate commission rate based on the sales figure shown in column C.

As usual, I'll start off by naming the range for the lookup table. I'll call it "commission_table". This will make our VLOOKUP formula a lot easier to read and copy.

Now let's use VLOOKUP to get the first commission rate for Applebee.

In this case, the lookup value is the sales number in column C. As always with VLOOKUP, the lookup value must appear in the left-most column of the table because VLOOKUP only looks to the right.

The table is our named range "commission_table".

For column, we need to provide a number for the column that contains the commission rate. In this case, that's the number "2".

Finally, we need to enter a value for range_lookup. If set to TRUE or "1" (which is the default) VLOOKUP will allow a non-exact match. If set to "0" or FALSE, VLOOKUP will require an exact match.

In this case, we definitely want to allow a non-exact match because the exact sales amounts will not appear in the lookup table, so I'll use TRUE.

When I enter the formula, we get a commission rate of 6%.

If we check the table, this is correct. From $125,000 to $175,000, the commission rate is 6%.

Now I can copy down the formula to get a commission rate for the remaining salespeople.

Since we have a commission rate, I can also add a formula that calculates the actual commission.

It's important to understand that if you allow non-exact matches with VLOOKUP, you must make sure that your table is sorted in ascending order.

With non-exact matches, VLOOKUP moves to the first value that's greater than the lookup value, then falls back to the previous value.

If I temporarily sort the commission table in descending order, the VLOOKUP formulas stop working properly and we get a lot of N/A errors. When I re-sort the table in ascending order, the VLOOKUP formulas work again.