# How to use the VLOOKUP function?

#### Frédéric LE GUEN

Last Updated on 02/09/2023

The VLOOKUP function needs 4 arguments

1. The value to search

It must be a single cell

2. The range of cells in your reference table

Where to look up the value to return

3. The column number to return

This step is mostly the main reason for errors with VLOOKUP

4. The type of research

Exact or Approximate research

The VLOOKUP function is one of the most used in Excel, and also the one which returns the most errors. Let's see how to use it. If the VLOOKUP function is not correctly written, the function returns N/A.

## Explanation step-by-step

You have a list of products with details for each one of them. We want to collect the Origin of each fruit, based on the product ID.

### Step 1: What we are looking for?

• We want to find a specific row.
• We will find this row by its ID ; the product ID
• So, the first argument of VLOOKUP is the ID of the row we are looking for.

=VLOOKUP(C13,

### Step 2: Range of cells of the reference table

This step is very easy. We just write the references of the area where you have your data

=VLOOKUP(C13,\$A\$2:\$E\$11,

### Step 3: What is the column to return

The third argument of the function is the column number you want to return. In our example, the column Origin is in the third position of our lookup table. So the value of the 3rd argument = 3.

=VLOOKUP(C13,\$A\$2:\$E\$11,3

### Step 4: Exact match (IMPORTANT)

The last argument must be equal to FALSE (or 0).

=VLOOKUP(C13,\$A\$2:\$E\$11,3 ,0)

This argument is important because if it is omitted, the value = 1, and 1 (or TRUE) means approximate research. You will see in this article an example of when to use argument 1 with the function VLOOKUP

## What happens when we change the search value?

When you change the value to search (first argument), the function returns instantaneously new data from the Origin column. In this example, we search the value ID_003, ID_006, ID_009

## What happens when we change the column index?

When we change the third argument, we return the contents of the corresponding column index in the reference table.

For instance, here, we change the index from 5, to 4, to 3, and to 2. Each time, VLOOKUP returns the data of the specified column.

## Dynamically change the column

You can customize the third argument with the MATCH function to create a dynamic research.

## Tutorial video

Have a look at this video to see a little girl explaining the VLOOKUP function. If a kid can understand the logic, you can understand it too 😊

1. Xiomara Gomez
17/10/2020 @ 01:26

This video was very helpful, it's been years i wanted to learn these and right on it for the sake of my child.

2. shukuntala
30/08/2019 @ 16:17

3. binoddai
07/06/2019 @ 10:32

very useful

4. Tharmalingam Thirunavukarasu
28/09/2018 @ 15:57

Excellent. Very useful. Thank you.

# How to use the VLOOKUP function?

Last Updated on 02/09/2023

The VLOOKUP function needs 4 arguments

1. The value to search

It must be a single cell

2. The range of cells in your reference table

Where to look up the value to return

3. The column number to return

This step is mostly the main reason for errors with VLOOKUP

4. The type of research

Exact or Approximate research

The VLOOKUP function is one of the most used in Excel, and also the one which returns the most errors. Let's see how to use it. If the VLOOKUP function is not correctly written, the function returns N/A.

## Explanation step-by-step

You have a list of products with details for each one of them. We want to collect the Origin of each fruit, based on the product ID.

### Step 1: What we are looking for?

• We want to find a specific row.
• We will find this row by its ID ; the product ID
• So, the first argument of VLOOKUP is the ID of the row we are looking for.

=VLOOKUP(C13,

### Step 2: Range of cells of the reference table

This step is very easy. We just write the references of the area where you have your data

=VLOOKUP(C13,\$A\$2:\$E\$11,

### Step 3: What is the column to return

The third argument of the function is the column number you want to return. In our example, the column Origin is in the third position of our lookup table. So the value of the 3rd argument = 3.

=VLOOKUP(C13,\$A\$2:\$E\$11,3

### Step 4: Exact match (IMPORTANT)

The last argument must be equal to FALSE (or 0).

=VLOOKUP(C13,\$A\$2:\$E\$11,3 ,0)

This argument is important because if it is omitted, the value = 1, and 1 (or TRUE) means approximate research. You will see in this article an example of when to use argument 1 with the function VLOOKUP

## What happens when we change the search value?

When you change the value to search (first argument), the function returns instantaneously new data from the Origin column. In this example, we search the value ID_003, ID_006, ID_009

## What happens when we change the column index?

When we change the third argument, we return the contents of the corresponding column index in the reference table.

For instance, here, we change the index from 5, to 4, to 3, and to 2. Each time, VLOOKUP returns the data of the specified column.

## Dynamically change the column

You can customize the third argument with the MATCH function to create a dynamic research.

## Tutorial video

Have a look at this video to see a little girl explaining the VLOOKUP function. If a kid can understand the logic, you can understand it too 😊

1. Xiomara Gomez
17/10/2020 @ 01:26

This video was very helpful, it's been years i wanted to learn these and right on it for the sake of my child.

2. shukuntala
30/08/2019 @ 16:17