# A Step By Step Guide For How To Use Vlookup Function In Excel

Updated on March 29, 2020

I have 7 years of experience working with excel spreadsheets. I think excel has data analytics capabilities comparable to no other tool.

## Excel : An Overview

Microsoft Excel is one of the most widely used computer applications software. It has proven to be immensely helpful when it comes to data management and data analytics. Moreover, it is very compatible with most of system application Softwares. All of us must have been a consumer to this DBMS in our lives. Here we will discuss the use of Vlookup tool available in this spreadsheet software for data analytics and comparison but before we start working on Vlookup we will have to take a look at its complementary function that is "Sort and Filter".

## Sort and Filter

The sort function is used for sorting data in a sequential manner so that the analyst could easily point out the outcomes. Similarly, the filtering allows the user to apply a filter on large datasheets for segregating the desired values from undesirable results. Both of these tasks are very helpful in organizing and analyzing large data in a timely manner.

How to use this command in Microsoft Excel?

Just press Alt+D+F+F on your keyboard while the datasheet is open on your PC.

After pressing this command a triangle symbol will appear on the topmost cell of all the columns. When you will click on this triangle symbol it will display many options to sort the data in this particular column from lowest to highest order or vice versa and filter by color, description, etc. as shown in the picture given below.

## VLOOKUP

Vlookup is one of the most powerful and important tools used in excel spreadsheets. This is used to compare the data from one spreadsheet to another one. Unlike other commands, it can also be executed from one excel file to another excel file. The data of one column can be compared to another column in the excel spreadsheet and the common variables are displayed against each other. I will illustrate it by solving the following problem :

Suppose you have an excel spreadsheet containing bill of materials(BOMs) of two distinct car models produced by a car manufacturing Factory. The name of the first model is "AA15" and the second one is "AA18". The sheets contain the part code, descriptions and quantity of components used to make the cars. The company made the model AA15 first and then after a few years they launched another one "AA18". The Engineering design team of both the vehicles says that they have used the design of AA15 as a reference and after doing some modifications in it they created a new model AA18. There are some components which are common in both the AA15 and AA18. Now if a person wants to extract a list of new parts that are used in new model AA18 they will have to compare the BOMs of both models. Since the BOMs are available in excel files so the required list can be prepared with the help of Excel's Vlookup tool. The step by step procedure of doing Vlookup on above-mentioned requirement is as follows:

• First of all open both the worksheets in Microsoft Excel with BOM "AA18" being displayed at the front end.

• In front of the first row of BOM "AA18" type the mathematical symbol "=" and then click on text Vlookup(which is shown in circle) in the formula bar as shown in the figure given below.

• After clicking on the Vlookup function, a dialogue box containing function arguments will appear as shown in the image given below.

• The functional dialogue box requires 4 arguments to be filled namely Lookup Value, Table Array, Column Index number and Range lookup. The first one is Lookup value which is the data that will be common with the other sheet. In this case, the material codes of BOM AA15 and AA18 will be a connection point between both of them so we will click on the first cell of the material code column. When we will click on this cell its border will start blinking with a dotted line.

• Now we will press the "tab" key on the keyboard and the cursor will move to the next functional argument that is Table_array. The table array corresponds to the data in the column of the other sheet which is to be used as a reference. In our case, the column "Material code" of BOM "AA15" will be the table array so we will click on the first cell in the Material Code column of the BOM AA15. It will also start blinking like the earlier one but now we will have to select all the cells in this column which have some data because every single value in BOM AA18 will be compared with all the cells in BOM AA15. Therefore we will drag the cursor from the first cell to the last one. If you want to get the data of "Material Description" being displayed as the final outcome then 2 or more columns are required to be selected.

• Again press tab key on keyboard and cursor will return to the dialogue box argument "Column index number". It corresponds to the column number right to the column selected in the table array. It means if you will enter a value of 1 then the result will be the value of the same column "Material Code" from BOM AA15 will be returned, if you will enter value 2 then it will return the value just right to the column Material code(i.e Material description) and so on.

• In the last argument of "Range lookup" enter the value 0, press enter and the cell shows the value from BOM AA15 that is matching the corresponding cell in BOM AA18.

• Select the cell and drag down till the last one.

• This is how the Vlookup function works, the values matching material code from BOM AA18 with AA15 will be displayed against the same and the ones that do not match will return the value "N/A". There will be different outcomes with Column index value filled as "1" and Column index value filled as "2" as shown in the images given below.

• As per our requirement, we want to eliminate the list of new added parts in BOM AA18 so we will have to filter the cells with N/A value. To do this we will press ALT+D+F+F on the keyboard and a triangle like symbol will appear on the topmost cell of each column.

• Now we will click on the triangle like symbol over the column of Vlookup values, unselect the "select all" box and select the "#N/A" box only.

• A single list of new added parts will be displayed.

We have practiced the comparison of two sheets with cells having the same number of characters. Sometimes a situation arises such that one cell has 1 or two characters lesser than the other one{e.g. 50004809AA(10 characters) and 50004809(8 characters)}. Vlookup is also helpful in such situations. This can be done as follows :

• Type the command "=left(" in front of the first cell of BOM AA15.

• Select the first cell in the column Material code.

• Press comma"," and type the number of characters that are required to be displayed beginning from the left side and close the bracket. Here we are taking the number "8".

• Press Enter and drag down the selection till the last row.

• All the Material codes with an 8 digit value will be displayed as shown in the figure given below.

• Now we will come back to the first cell in of BOM AA18 and apply the Vlookup function. The Lookup value argument will be filled as "Left(cell number, number of digits required to be compared). We have applied the formula {Left(D5,8)}.

• To fill the "Table Array" we will select the newly created 8 digit column in BOM AA15.

• The other two values(Col index num and Range lookup) will remain the same(eg 1,0) and the 8 digit comparable values will be displayed against the corresponding 10 digit material codes".

See results

12

4

0

5