VLOOKUP function in Microsoft Excel is used to look up the values vertically (leftmost column) in the given range and find the matching entry. If the matching entry is found, it returns the entry from the given column in the same row where it found the look-up value.
The syntax of the VLOOKUP function is:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]);
Where lookup_value is the value you want to search for. You can pass cell reference also as a look-up value.
table_array is the range where to search for lookup_value.
col_index_num is the column index from which it has to return the value if it finds lookup_value within the table_array.
range_lookup is the optional parameter used to tell VLOOKUP whether to look for an exact match or an approximate match. If you pass FALSE as the parameter value VLOOKUP function will search for an exact match; otherwise, it will look for an approximate match.
Let us take the below example:
Suppose you have the following master data in your Worksheet.
Your requirement is to fill in the passwords in the below worksheet for each row in the Password column.
Now how do we do this? One way is, we can manually search for each value that is associated with each record in master data and copy the password and paste it into the Worksheet.
Another simple way is to use VLOOKUP a function to find the matching entries. How do we do it? Below are the steps:
Step (1). Start typing = and VLOOKUP function in the cell where you want to display the result. Once you start typing the function name, Microsoft Excel will display the list of functions in the drop-down box. Select VLOOKUP.
Step (2). Provide the lookup_value. As explained above, lookup_value is the value we want to search for. In this case Record#. So select cell reference A2, because Record# values start from A2. We have to look for the value that is there in A2 in the master data.
Step (3). Now we need to select the range for table_array which is our master data. In our master data, we have only 2 columns. So select column Record# and Password columns.
Step (4). Once VLOOKUP finds the look-up value, we need to tell what column value VLOOKUP function has to return from the master data. We need a Password value for a particular Record#. So Password column is the 2nd column; we need to pass 2 as the value for col_index_num.
Step (5). Finally, we need to set VLOOKUP to search for exact matches. So the last function parameter should be FALSE (if you want to search for the approximate match you can pass TRUE as the value. But in this case it should be an exact match; so we have to pass the value “FALSE”). VLOOKUP search for exact matches and return the results to the cell from where VLOOKUP function is called.
Nice one!