VLookup

 

Exact Match | Approximate Match | First Match | Case-insensitive | Multiple Lookup Tables

The VLOOKUP function is one of the most popular functions in Excel. This page helps you understand the VLOOKUP function quickly and easily.

Exact Match

Most of the time you are looking for an exact match when you use the VLOOKUP function in Excel. Let's take a look at the arguments of the VLOOKUP function.

1. The VLOOKUP function below looks up the value 53 (first argument) in the leftmost column of the red table (second argument).

VLookup Arguments

2. The value 4 (third argument) tells the VLOOKUP function to return the value in the same row from the fourth column of the red table.

VLookup Result in Excel

Note: the Boolean FALSE (fourth argument) tells the VLOOKUP function to return an exact match. If the VLOOKUP function cannot find the value 53 in the first column, it will return a #N/A error.

3. For example, change the ID of Jessica to 54.

#N/A Error

4. Here's another example. Instead of returning the salary, the VLOOKUP function below returns the last name (third argument is set to 3) of ID 79.

Exact Match

Approximate Match

Let's take a look at an example of the VLOOKUP function in approximate match mode (fourth argument set to TRUE).

1. The VLOOKUP function below looks up the value 85 (first argument) in the leftmost column of the red table (second argument). There's just one problem. There's no value 85 in the first column.

VLookup Function in Approximate Match Mode

2. Fortunately, the Boolean TRUE (fourth argument) tells the VLOOKUP function to return an approximate match. If the VLOOKUP function cannot find the value 85 in the first column, it will return the largest value smaller than 85. In this example, this will be the value 80.

Largest Value Smaller than Lookup Value

3. The value 2 (third argument) tells the VLOOKUP function to return the value in the same row from the second column of the red table.

Approximate Match in Excel

Note: always sort the leftmost column of the red table in ascending order if you use the VLOOKUP function in approximate match mode (fourth argument set to TRUE).

First Match

If the leftmost column of the table contains duplicates, the VLOOKUP function matches the first instance. For example, take a look at the VLOOKUP function below.

First Match

Explanation: the VLOOKUP function returns the salary of Mia Clark, not Mia Reed.

Case-insensitive

The VLOOKUP function in Excel performs a case-insensitive lookup. For example, the VLOOKUP function below looks up MIA (cell G2) in the leftmost column of the table.

Case-insensitive Lookup

Explanation: the VLOOKUP function is case-insensitive so it looks up MIA or Mia or mia or miA, etc. As a result, the VLOOKUP function returns the salary of Mia Clark (first instance). You can use the INDEX, MATCH and the EXACT function in Excel to perform a case-sensitive lookup.

Multiple Lookup Tables

When using the VLOOKUP function in Excel, you can have multiple lookup tables. You can use the IF function to check whether a condition is met, and return one lookup table if TRUE and another lookup table if FALSE.

1. Create two named ranges: Table1 and Table2.

First Table

2. Select cell E4 and enter the VLOOKUP function shown below.

VLookup Function with Multiple Lookup Tables

Explanation: the bonus depends on the market (UK or USA) and the sales amount. The second argument of the VLOOKUP function does the trick. If UK, the VLOOKUP function uses Table1, if USA, the VLOOKUP function uses Table2. Set the fourth argument of the VLOOKUP function to TRUE to return an approximate match.

3. Press Enter.

4. Select cell E4, click on the lower right corner of cell E4 and drag it down to cell E10.

Copy VLookup Function

Note: for example, Walker receives a bonus of $1,500. Because we're using named ranges, we can easily copy this VLOOKUP function to the other cells without worrying about cell references.