↑ Return to Find

VLOOKUP (Exact Match)

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 the contents of the first column.

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 the data linked to the first data (like the price of a product, the adress of the customer, ...)

To build a VLOOKUP, you needs 4 parameters

  • The first parameter of the function contains a value you want to search.
  • The second parameter is the range of cells where you want to make your research.
  • The 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).

 

Remark

Before detailing each component, it is necessary to dwell on the last parameter which is optional but very useful. Depending on the value (0 or 1) the function will search an exact or approaching data in your table. You will find an example of an approximate research in this page.

 

Construction

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

Function of the 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 :

=VLOOKUP(C1;

 

 

2ed step: you select the range of cells that contain your data's reference.

=VLOOKUP(C1,$B$5:$E$15

One think is compulsory when you select your range of cells ; the first column MUST contains the 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 the references of your table because reference must be always the same.

3rd step : you specify the 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 the header of the Excel's column. So, here we want to return the name of the currency, we will indicate in column 3 because it is the third column in our selection.

=VLOOKUP(C1,$B$5:$E$15;3

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

=VLOOKUP(C1,$B$5:$E$15,3,0)

With the result



Now try to return the name of the country when the value in C1 is equal to HU in the document below.

Leave a Reply