Usage of VLOOKUP function in Microsoft Excel!

VLOOKUP function in Microsoft Excel can be used to look up the values vertically (leftmost column) in the given range and find for the matching entry. If matching entry found, it returns the entry from the given column in the same row where it found the look up value.

The syntax of the 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 is used to tell VLOOKUP whether to look for exact match or approximate match. If you pass FALSE as the parameter value VLOOKUP function will search for exact match otherwise it will look for approximate match.

Let us take the below example:

Suppose you have the following master data in your worksheet.

Microsoft - Excel - VLOOKUP - Master data

Microsoft – Excel – VLOOKUP – Master data

Your requirement is filling the passwords in the below worksheet for each row in the Password column.

Microsoft Excel - VLOOKUP - New data

Microsoft Excel – VLOOKUP – New data

Now how do we do this? One way is, we can manually search for each value which is associated with each record in master data and copy the password and paste it in the worksheet.

Other simple way is use VLOOKUP function to populate the passwords. How do we do it? Below are the steps:

Step (1). Start typing 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 starts from A2. We have to look for value which is there in A2 in 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 Password value for 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 last function parameter should be FALSE (if you want to search for approximate match you can pass TRUE as the value. But in this case it should be exact match; so we have to pass the value “FALSE”). VLOOKUP search for exact matches and return the results into the cell from where VLOOKUP function called.

by Code Steps

1 comment for “Usage of VLOOKUP function in Microsoft Excel!

  1. mourya
    February 13, 2014 at 4:01 PM

    Nice one!

Leave a Reply