How to use VLOOKUP in Excel (All Edition)

VLOOKUP stands for ‘Vertical Lookup’. It is a function that makes Excel search for certain data in a column (the so-called ‘table array’) to return a value from a different column in the same row.

A VLOOKUP function exists of 4 components:

  1. The value you want to look up;
  2. The range in which you want to find the value and the return value;
  3. The number of the column within your defined range, that contains the return value;
  4. 0 or FALSE for an exact match with the value you are looking for; 1 or TRUE for an approximate match.

Syntax: VLOOKUP([value], [table “” not found /]
, [column number], [true or false])

How to use VLOOKUP in Excel Example:-

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 (1st argument in H2) in the leftmost column of the red table (2nd argument in B3:E9).

How to use VLOOKUP in Excel

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

How to use VLOOKUP in Excel

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

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

How to use VLOOKUP in Excel

Approximate Match

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

1. The VLOOKUP function below looks up the value 85 (1st argument) in the leftmost column of the red table (2nd argument). There’s just one problem. There’s no value 85 in the 1st column.

How to use VLOOKUP in Excel

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

How to use VLOOKUP in Excel

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

How to use VLOOKUP 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 (4th argument set to TRUE).

Vlookup Looks Right

The VLOOKUP function always looks up a value in the leftmost column of a table and returns the corresponding value from a column to the right.

1. For example, the VLOOKUP function below looks up the First name and returns the last name.

How to use VLOOKUP in Excel

2. If you change the column index number (3rd argument) to 3, the VLOOKUP function looks up the first name and returns the salary.

How to use VLOOKUP in Excel

Note: in this example, the VLOOKUP function cannot lookup the first name and return the ID. The VLOOKUP function only looks to the right. No worries, you can use INDEX and MATCH in Excel to perform a left lookup.

First Match

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

How to use VLOOKUP in Excel

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

Vlookup is 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.

How to use VLOOKUP in Excel

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 (1st instance). Use INDEX, MATCH and EXACT in Excel to perform a case-sensitive lookup.

Multiple Criteria

Do you want to look up a value based on multiple criteria? Use INDEX and MATCH in Excel to perform a two-column lookup.

How to use VLOOKUP in Excel

Note: the array formula above looks up the salary of James Clark, not James Smith, not James Anderson.

#N/A error

If the VLOOKUP function cannot find a match, it returns a #N/A error.

1. For example, the VLOOKUP function below cannot find the value 28 in the leftmost column.

How to use VLOOKUP in Excel

2. If you like, you can use the IFNA function to replace the #N/A error with a friendly message.

How to use VLOOKUP in Excel

Note: the IFNA function was introduced in Excel 2013. If you’re using Excel 2010 or Excel 2007, simply replace IFNA with IFERROR. Remember, the IFERROR function catches other errors as well. For example, the #NAME? error if you accidentally misspell the word VLOOKUP.

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.

How to use VLOOKUP in Excel

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

How to use VLOOKUP in Excel

Explanation: the bonus depends on the market (UK or USA) and the sales amount. The 2nd argument of the VLOOKUP function does the trick. If UK, the VLOOKUP function uses Table1, if USA, the VLOOKUP function uses Table2. Set the 4th 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.

How to use VLOOKUP in Excel

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.

Index and Match

Instead of using VLOOKUP, use INDEX and MATCH. To perform advanced lookups, you’ll need INDEX and MATCH. Maybe this is one step too far for you at this stage, but it shows you one of the many other powerful formulas Excel has to offer.

How to use VLOOKUP in Excel

Xlookup

If you have Excel 365, use XLOOKUP instead of VLOOKUP. The XLOOKUP function is easier to use and has some additional advantages.

How to use VLOOKUP in Excel

For any query or complaint mail at [email protected]

Follw on Twitter

Like on Facebook

Follow on Instagram