The VLOOKUP function needs 4 arguments

**The value to search**It must be a single cell

**The range of cells in your reference table**Where to look up the value to return

**The column number to return**This step is mostly the main reason for errors with VLOOKUP

**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,

**Note 1:**The first column MUST contain the value we are searching.**Note 2**: It is not necessary to include the header in your array.**Note 3**: You must block the references of your lookup table.

### 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 and select any column of your table.

- Why VLOOKUP returns #N/A?
- Function XLOOKUP in Excel
- How to use the INDEX function in Excel?
- How to use the MATCH function in Excel?
- VLOOKUP to the left in Excel

## 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 😊

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.

shukuntala

30/08/2019 @ 16:17

thanks for video its very helpful but please provide sum datta so can practice can you please help me with formate

binoddai

07/06/2019 @ 10:32

very useful

Tharmalingam Thirunavukarasu

28/09/2018 @ 15:57

Excellent. Very useful. Thank you.