Excel could be a database with VLOOKUP

Once you have a table with one or more columns, you can use VLOOKUP to returns one of the values in this table by referring to contents of first column.

Tutorial video

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

Principle of the function VLOOKUP

Excel can be used as a database. Often, Excel files store data like customers, prospects or products and you want to return data linked to first data (like price of a product, adress of customer, ...)

To build a VLOOKUP, you needs 4 parameters

  • First parameter of the function contains a value you want to search.
  • Second parameter is the range of cells where you want to make your research.
  • Third parameter indicates the column number you want to return.
  • The last parameter is 0 or FALSE (means exact match) or 1 or TRUE (means approaching).

Example with VLOOKUP

For example, take the following table where the column B contains country ISO code, in C the name of country, currency name in D and in E currency change parity.

In Function of value of the ISO code in C1, we want to return the name of the currency in C2.

We start by writing the data we want to search. So we write :

2ed step: you select range of cells which contains your data's reference.


One think is compulsory when you select your range of cells ; the first column MUST contains data you look for. If you select for instant $A$5:$E$15, your function can't work.

Note 1: It is not necessary to include the header in your array.

Note 2: It is recommended to block references of your table because reference must be always the same.

3rd step : you specify column number you want to returned as a result. The column number is the column number of your reference table and it could not corresponding to header of the Excel's column. So, here we want to return name of the currency, we will indicate in column 3 because it is the third column in our selection.


Finally, as we get exactly the content of the third column for data CH, we want to return an exact ; so parameter 0 is needed. Final formula is


With the following result

Remark about the last parameter

In 99% of the cases, the VLOOKUP function will expect the value 0 for an exact match.

But if you have to return value in a range, it's compulsory to use the parameter TRUE. You will find an example of an approximate research in this page.

Related posts

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

1 comment

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