VLOOKUP Formula in excel with example

How to use VLOOKUP function in excel

VLOOKUP formula allows the merging of two sheets using a common field to match data.

Excel has many important functions which save your precious time when you work on the data you have. VLOOKUP is one of these important formula. With VLOOKUP formula you can merge two sheets with a common column, which means you don’t need to type the data again for the same column value. 

 

Points to remember for VLOOKUP formula

It is a good practice to have both the sheets on the same excel file.

Consider the file in which the master data is present as master sheet and to where the data of other columns are to be copied using VLOOKUP formula is considered to be new datasheet.

The same column in both the files must be in the 1^st column otherwise. The VLOOKUP function will not work.

VLOOKUP Formula parameters

There are total four parameters for the VLOOKUP function to configure:

1. lookup_value:  it is generally the address of the 2^nd cell of the common column in both the sheet.

2. table array : it is the range of selected data from which you need to do VLOOKUP in the new datasheet.

3. col_index_num: it is the index number of the column which data you want on your new datasheet.

4. range_lookup: in this tab you can either enter true or false. If you enter true it also copy the approximate value. If you enter false it will only show data when there is exact match in the same column otherwise it will show you  #N/A.

We have included two pictures of our sheets master datasheet and new datasheet in figure 1 and 2 respectively. 

VLOOKUP FORMULA EXAMPLE

                                         

                                           

We want the value of label city to be appearing in the second column of the new datasheet. Here we are doing this using VLOOKUP function.

Open the VLOOKUP Dialog box using fx button on the toolbar.

Select the VLOOKUP function on the list.

Now the VLOOKUP dialog box will appear.

                                

There you will have four parameters as discussed above.

Now you need to fill the value in the tabs.

Lookup value: in our case A2

TABLE ARRAY:  In table array we need to select all the data from the master sheet. The address of the data will be shown in the dialog box.

Col_index_num: in this you need to enter the column no of which data you want on your new sheet. In our case it is 2.

Range_ lookup: we have used 0 for false. As we need exact data on the new sheet. 

Now we have learnt vlookup formula with a example. 

Thanks for visiting.