Home » Function » Lookup functions » How to use the VLOOKUP function?

How to use the VLOOKUP function?

Reading time: 3 minutes
Last Updated on 03/05/2023 by Frédéric LE GUEN

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).
What is the logic of the VLOOKUP function

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.

Table of Products

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 😊

4 Comments

  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.

    Reply

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

    Reply

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

    very useful

    Reply

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

    Excellent. Very useful. Thank you.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

How to use the VLOOKUP function?

Reading time: 3 minutes
Last Updated on 03/05/2023 by Frédéric LE GUEN

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).
What is the logic of the VLOOKUP function

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.

Table of Products

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 😊

4 Comments

  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.

    Reply

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

    Reply

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

    very useful

    Reply

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

    Excellent. Very useful. Thank you.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *