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).
Before detailing each component, it is necessary to dwell on the last parameter which is optional but very useful. Depending on 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.
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.
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 following result
Now try to return name of the country when value in C1 is equal to HU in document below.