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