By Bob Umlas, Excel MVP

Using VLOOKUP in Excel seems to be a mystery to many. But you do it all the time in real life—you look up a word in a dictionary and scan a little to the right to read the definition. Well, it could be the same thing in Excel:

If you wanted to use Excel to look up a word in this dictionary, the syntax would look something like this:

The syntax is =VLOOKUP(what,where,returning which column,approximate match?)

“What” you’re looking up is cell G1, or Banana.

“Where” you’re looking it up is in the dictionary which is A1 thru B5 (A1:B5)

“Returning which column” is from the second column, or 2 (not “B”, because Excel’s syntax requires a number, not a letter).

“Approximate Match” is FALSE because we want an exact match.

Let’s say I looked up “Grape” in this dictionary:

The #N/A indicates it’s “Not Available”. Therefore “Grape” is not in this dictionary.

You may be wondering, “Why would anyone want an approximate match?” Well, suppose you’re looking up your salary in a tax table, and suppose you make $75,000. Here’s the fictitious table:

Your $75,000 isn’t in the table. It would be wrong here to get a #N/A error! You do owe taxes! You owe 12% of the first $60,000, and 12.75% of anything over that. So we’d use the TRUE instead of FALSE in the VLOOKUP formula (Since it’s assumed TRUE if omitted, we can just omit it):

The .12 is the 12% found next to the $60,000 item (cell B4) because we didn’t ask for an exact match. (When you do ask for the approximate match, the lookup column must be in sequence). Note that Excel does not also pick up the formatting, so the 12% is shown as .12. The value is the same, of course. Also note that the range we’re looking at is A2:C7 – we don’t include the headings in row 1.

In the following figure, we see that the 12% is for the first $60,000, so we need to “find” that $60,000:

Then these 2 values in column F need to be multiplied together to get the base tax amount:

The remaining part is to find the amount over the $60,000, then the tax on that overage. This is seen here:

This is a little trickier as we’re looking at a few calculations simultaneously. The difference is E1 minus F2; the $75,000 less the $60,000. This is places in parentheses so we can multiply that difference by the tax found in the 3^rd column, the VLOOKUP(E1,A2:C7,3). This gives the result of $1912.50.

The total tax can now be found by adding the overage to the base:

For you adventurous folks, here’s the same answer all done in one cell:

A few final notes:

If the table started in column J, the column reference would still be 1, 2, or 3, not 10, 11, and 12, (the column number for J, K, and L) because the formula is looking for the relative column in the range. The above formula would be:

Learn more about IIL’s Microsoft Excel training

Bob Umlas has been voted an “MVP” (Most Valuable Professional) by Microsoft each year since 1994 for his contributions to Excel online forums and he is known world-wide for his expertise. As an MVP, he meets yearly with fellow MVPs at Microsoft’s headquarters in Redmond, where he has access to the product developers. He has also been a beta tester for new versions of Excel since version 1.5 and is the author of several books including This isn’t Excel, it’s Magic! (available on the IIL Bookstore), Excel Outside the Box, and More Excel Outside the Box.