Excel Worksheet Functions: VLookup

The Excel VLookup (Vertical Lookup) worksheet function is versatile, has database-like features and can be tricky to use correctly. I always have to look in Excel Help to get this worksheet function right. The best way to see how this function works is to do Insert, Function. In the "Or select a category" drop down select the "All" category. In the "select a function" then type a "v" and scroll down to VLOOKUP. Click the Help on this function link.

So to see how the Excel worksheet function VLookup works, let's setup a range that looks like:


Figure 1. Where we will be doing our VLOOKUP.

The VLOOKUP function requires at least three and up to four arguments. The syntax for the VLOOKUP function is
=VLOOKUP("value that we are looking for", "worksheet range to look in", "if we find what we are looking for go over this many columns and return the value in that column to this worksheet cell", "do we want an exact match or not?")
Let's look at some examples.


Figure 2. VLOOKUP examples.

In Example 1, we want to lookup "ky" in the range defined by cells B2 to E3. VLOOKUP always looks down the first column of the range, in this case column B. If we find an exact match for "ky" we want to look over 2 columns (column B is the 1st column) and return what we find. In this case we return the full state name "Kentucky". The actual VLOOKUP function is in cell E7. The text of the function (so you can see what the syntax looks like) is in cell G7.

Example 2 is similar to, but more versatile than, Example 1. We are still looking for an exact match for "ky". But this time we don't "hard code" the value "ky" in the first argument. We make reference to the cell address that "ky" is in, namely cell C8. So the first argument of the VLOOKUP function has C8 in it. And we don't hard code the column number 2 that we want to look over across. We make reference to the cell the value 2 is in, namely cell D8. So the third argument of the VLOOKUP function has D8 in it.

In Example 3, we change what we are looking up to "oh" and we change how many columns we want to go across and return once we find an exact match. In this case we want to go over to column E and return what is in the row that contains "oh" in column B.

Finally, in Example 4, we try to find "ma" in column B and are unsuccessful. So the VLOOKUP function returns the special value #N/A.

VLOOKUP using an inexact match

You may wonder why we would want to do an inexact match. Calculating sales commissions is a good example.


Figure 3. VLOOKUP examples with an inexact match.

A company has a sales commission that depend on the sale amount. The commission increases a various steps or levels. Sales from $100 to $199 result in a %1 sales commission. Sales from $500 to $999 result in a 3% commission. So to find out how much of a commission $150 generates, we do a VLOOKUP with the fourth argument as TRUE (which happens to be the default value for the fourth argument, so beware, especially if you really want to do an exact match). Since $150 is in between $100 and $500, it returns the sales commission for $100. Similarly, a VLOOKUP for $750 returns a sales commission of %3. We omitted the fourth argument value (TRUE) do demonstrate that TRUE is the default value.