Have you ever used VLOOKUP in Excel? If you haven’t then you are missing the most important feature of Excel. VLOOKUP is an extremely powerful and useful function which once you have mastered might come in handy for looking up data that is in different worksheets or even in a completely different work book.
The concept of VLOOKUP is not only applicable for Excel but also been used in variety of data related soft wares like Google Spreadsheet, Open Office, etc.
What is a VLOOKUP?
VLOOKUP (Vertical Lookup) is one among the lookup and reference functions in Excel that could help you to find particular information in your excel sheet. The simplest example that can be cited is a telephone directory where you use the person’s name to lookup their telephone number. Let us take a look at a practical example so that you could understand the concept better.
I have an Excel Workbook with two sheets,
- Employee Details
- Pay Structure
In “Employee Details” sheet I have the following columns Emp ID, Employee Name, Designation and Salary. I have data prefilled in for all the columns except the Salary column.
Now coming to the second sheet “Pay Structure” we have Designation and Salary columns. The Salary column has some predefined values based on the Designation column.
We are going to populate the Salary column in the Employee Details sheet by looking up the data in the Pay Structure sheet based on Designation. In real word scenario, a company may have thousands of employees and hence referring the Pay Structure sheet and putting the salary in the Employee Details sheet for each designation is not only a manual task but also can lead to errors. Now let us quickly see how this could be accomplished using VLOOKUP.
Download the VLOOKUP Example sheet and just follow the below instructions to get the desired result.
Note: Once you have downloaded the file, right click on the downloaded file and click Properties. In the property window click on the Unblock button and click OK. This is required in-order to make changes to the excel file.
Alternatively you can also directly open the file and click on the Enable Editing button.
There are two ways to invoke the VLOOKUP formula. First you can directly type in the formula into the cell and second you can access the function from the Formulas Menu.
How to use vlookup in Excel
Go to the Employee Details sheet and place your cursor on the D2 cell (Salary Column Row2) and navigate to the Formulas Tab and click on the Insert Function block.
You can also directly invoke this function window using the SHIFT + F3 shortcut key.
The function dialog box will be displayed to you and in the Search for a function text area, enter “vlookup” and click on the Go button. You can also find the VLOOKUP function under the Lookup & Reference category.
Note: If you notice the Salary column cell that you had selected an equal to (=) symbol has been automatically included when the Insert function window was opened. All formulas begin with an equal to symbol.
Now you should see the VLOOKUP function in the Select a function list. Select VLOOKUP and click OK button.
You should now see the Function Arguments window displayed to you and there are four values that you need to input in order to complete your VLOOKUP formula. Let us look into them one by one:
Lookup_value – This is the input value that you would like to use in order to get the value associated with it. In our example we need the Salary for the Designation so the Lookup_value should be C2 from Employee Details sheet whose value is the Project Manager. You can either type C2 directly or select the C2 cell and then press tab so that your cursor will now be in Table_array.
Table_array – This the source on which you would like to use the Lookup_value. In our example we have the Project Manager in C2 which you have provided as Lookup_value, now this input value has to be matched against the data which is in our Pay Structure sheet. So just click on the Pay Structure sheet and select A1 till B7 and then press TAB to move on to the next argument.
Col_index_num – The value that we need is present in the second column (salary) of the Pay Structure sheet. So enter 2 for the col_index_num argument and press TAB key to move to the next argument.
Range_lookup – This is a Boolean field and hence accepts only TRUE / 1 or FALSE / 0. TRUE will give you the closest match and FALSE will return an exact match. In our case enter FALSE / 0.
Now that you have completed all of the above steps you should now see D2 (Salary for Project Manager) populated with 85,000 as you can see in the below screenshot.
Now when you drag the formula from cell D2 till D11 you might notice that from D6 you will get the #N/A error as you can see in the below screenshot.
The above issue happens because the cell references have changed. If you click on the D6 cell and look into the formula bar you might see the below line:
As you can see, the references to the Pay Structure sheet have changed to A5 to B11. But if you look into the Pay Structure sheet there are no Salary details for the Application Developer Designation in the A5 to B11 range. We don’t want the reference data (table array) to change. It should always be A1 till B7 in Pay Structure sheet.
So how do we fix the above issue? It’s quite simple, just click on the D2 cell and in the formula bar click put $before A and another $ before 1. You can also simply press F4 to automatically put the dollar signs before the Cell References. Now do the same for the B7 cell reference too.
Once you are done, your D2 cell formula should look like,
Now drag the formula to the rest of the cells below and you should have no error.
Video Demo on how to use Vlookup in Excel
You can refer how this is being done by watching the below video demo.
Now let us quickly see how to use VLOOKUP function by directly entering the formula into the cell. This should be pretty easy for you since you have already mastered the basics of VLOOKUP.
Place your cursor directly in the D2 cell and enter the equal to symbol (=). Now start typing vlookup and the formula will be automatically displayed. Just hit the TAB key.
You should now see the same 4 arguments that you had entered in the argument function window as we discussed above. After entering each argument press the comma symbol (,) to move to the next argument. once you have entered all the arguments press the ENTER key.
That’s it, you have now successfully learned how to use VLOOKUP in Excel. If you have any questions or clarifications kindly leave it in the comments and we will respond to you at the earliest.