Excel could be a database with VLOOKUP

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, you needs 4 parameters

  • First parameter of the function contains the value to search.
  • Second parameter is the range of cells where of your reference table.
  • Third parameter indicates the column number to return.
  • The last parameter is 0 or FALSE (means exact match) or 1 or TRUE (means approaching).
Logic of the VLOOKUP function

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 the reference of this cell

=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 equal 0 (or FALSE).

=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's happen when we change the first value?

When you change the value to search (first argument), the function returns instantaneously the Origin value.

What's happen when we change the column index?

When we change the third argument, we return the contain of the corresponding column number in the reference table.

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

Tutorial video

Have a look at this video so see a little girl explaining the VLOOKUP function.

Related posts


Permanent link to this article: https://www.excel-exercise.com/use-excel-like-database-vlookup/


2 comments

    • binoddai on 07/06/2019 at 10:32
    • Reply

    very useful

    • Tharmalingam Thirunavukarasu on 28/09/2018 at 15:57
    • Reply

    Excellent. Very useful. Thank you.

Leave a Reply

Your email address will not be published.