Lookup functions in excel are the functions that are used to retrieve some value from different table/sheet/workbooks based on a unique column or we could say primary key.
VLOOKUP()
VLOOKUP function in excel stands for vertical lookup, is very frequently used to find specific value that is located in a spreadsheet table.
VLOOKUP is one of the most commonly used functions that enable us to return a value from a table that is related to the data you have, and these two data table may be located on different sheets or different workbooks also.
Below two tables show product ids, their suppliers, descriptions and price, the challenge here is that they all are not available in a single table and we are required to get the product price from Price Table to Product Table. How do we do that? Fortunately VLOOKUP function has the answer for it.
As we could see in both the tables we have a common field also known as unique key, which is also one of the criteria to use VLOOKUP function, we cannot use VLOOKUP until we have some common field in relevant tables.
The syntax of VLOOKUP is
=VLOOKUP(lookup value, lookup table range, value column, Range_lookup)
Let’s understand each argument of the VLOOKUP:
The first argument, the lookup value, is the value that is common in both the tables, which is also a unique key so in this case it would be PID.
The second argument is lookup table range is the range of the table that contains the lookup table.
The values in the first column of the table are the values searched by lookup_value. So if the unique column or common column is not available at the first position in the table range, it will not work, so make sure the PID column is at the first column in the table.
Finally, the value column which is 3rd argument in the formula denotes column number to indicate which column of the table is to be used for the value, and since we are looking to get price from Price table and it is second column in our PRICE table, we will enter third argument as 2.
At lats, Range_lookup specifies whether you want VLOOKUP to find an exact match or an approximate match, enter 0 or FALSE.
Below table shows that we have entered VLOOKUP formula to get Price value from Price Table.
First argument is PID since it is the field which is available in both the tables.
Second argument is table range; I have locked the reference by pressing F4.
Third argument is 2 as the price is positioned at column 2 of the price table.
Fourth argument is FALSE as we need only exact matches.
You could see above that we have pulled the price value from one table to another table using VLOOKUP.
