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

## Purpose of the function VLOOKUP

Often, Excel files contain data like customers, prospects, or products. The VLOOKUP function returns the value of one cell of your reference table.

To build a VLOOKUP function, you need 4 parameters

- The first argument of the function contains
**the value to search**. - Second argument is
**the range of cells where of your reference table**. - Third argument indicates
**the column number to return**. - The last argument is 0 or FALSE (means exact match) or 1 or TRUE (means approaching).

If the VLOOKUP function is not correctly written, the function returns N/A.

## Explanation step by step

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

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

We want to focus on one row identify by one ID. So in C13, we have written 1 ID of our list. So **the first parameter** of the function **is what we search **(wording or cell reference)

=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 references of your reference table.

### Step 3: Index of 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 reference 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 when to use the argument 1 with the function VLOOKUP

## What happens when we update the value to search?

When you change the value to search (first argument), the function returns instantaneously a new data from the **Origin **column.

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

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.

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

07/06/2019 @ 10:32

very useful

28/09/2018 @ 15:57

Excellent. Very useful. Thank you.