I have already written an article on Excel VLookUp which could help beginners understand what it is and how it can be used. That article is just a start and this article will help you move to the next step. This article will help you to understand how to create a VLookup between two sheets in excel. This article is not targeted for the Excel Advanced Users / Experts but novice users who struggle to understand basic stuff.
I have a sheet called “Source Data” where all the list of Employees is available along with their Location and Role.Now I have another sheet called “LookUp” where I have few names of employees whose Location is required which is available in “Source Data” sheet.
Please note that since this is just an example we have a very few employees in source data and lookup sheets, this may not be the same case in real time scenario, so when the data is huge VLookUp will help you get things done.
Steps To Create VLookUp between Two Sheets in Excel
- Go to the LookUp sheet and click on cell G9.
- Type =vl which will bring up VLOOKUP function, now hit TAB key to auto-complete the function as shown below.
- Now the first parameter of the VLOOKUP function is the lookup_value – the value for which you need details. So in our case cell F9 has the employee name so click on the cell F9 and press comma.
- The next parameter of the VLOOKUP function is the table_array – the source data against which the look up can be performed. In our case the “Source Data” Sheet has the details of all the employees and their details, so click on “Source Data” sheet and select the columns D, E and F completely as shown below and press comma.
- The next parameter in the VLOOKUP function is the col_index_num – the column from which you would like to fetch and display the values. In our case this is Location column so enter the value as 2.
- The last parameter in the VLOOKUP function takes the values TRUE / FALSE value (or) values 1 or 0. Enter 0 and close the parenthesis.
As you can see from the above screenshot we have got the locations for each of the given employee. The last employee Z has the location value as #N/A, this is because the employee is not found in the source sheet.