VLOOKUP from Different Sheets
In general, we will get data in multiple worksheets, and having all the information in the single sheets itself is not possible in 99% of the cases. When the data scattered to different worksheets, it is not that easy to move between sheets and pull the data from different sheets. When we are pulling the data, VLOOKUP is vital, and as a new learner, you don’t have to try to fetch the data from one worksheet to another. However, in this article, we will show you how to apply VLOOKUP to fetch the data from different sheets.
Example of VLOOKUP Function on Different Sheets
Let’s recollect what the VLOOKUP formula does in general. Below is an example of how to use VLOOKUPHow To Use VLOOKUPThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address are unique identifiers. with different sheets on excel.
- Look at the below data in excel.
We have two tables, “Data Table” and “Result Table.”
- In “Data Table, we have all the values, and in “Result Table,” we have only Employee Name, so based on this, we need to fetch other details like “DOJ, Dept, and Salary.”
Below is the syntax of the VLOOKUP function.
- Lookup Value: Based on what value you are looking for, the required column in the other table. In this case, our lookup value is Employee Name.
- Table Array: This is nothing but from which table we are looking for the targeted column. In this example, Table Array is the data range of Data Table in ExcelData Table In ExcelA data table in excel is a type of what-if analysis tool that allows you to compare variables and see how they impact the result and overall data. It can be found under the data tab in the what-if analysis section..
- Column Index Number: In the selected Table Array from which column we need the result. To get the DOJ, it will be the 2nd column. For Dept, it will be 3rd Column, and for the Salary column, it will be the 4th column.
- Range Lookup: In this parameter, we need to select the kind of lookup value, i.e., either approximate or exact match. If we need an approximate match parameter, will TRUE or 1, in case of the exact match parameter, will be FALSE or 0.
To fetch DOJ, open the VLOOKUP function in the G3 cell.
- The first argument is Lookup Value, so our lookup value is Employee Name, so select F3 cell.
- Table Array will be the“Data Table” range from A3 to D11.
- Once you have selected the Table Array, make it an absolute reference by pressing the F4 key.
- Next up is the col index num, so from the table array selection column number will be 2nd column, so mention the same.
- Next is Range Lookup. We have two options, i.e., TRUE or FALSE. Since we need an exact match to give the criteria as FALSE or 0.
- Ok, we are done. Close the bracket and hit the enter key to get the result.
- We have got DOJ, copy the formula and paste to the next cell. This time we need not apply the formula again. We just need to change the column index number from 2 to 3.
- Similarly, for the “Salary” column, change the column number to 4.
So, we have got all the three required details in the single formula itself. Now we will see how to apply VLOOKUP to fetch data from one worksheet to another worksheet.
How to Use VLOOKUP with Different Sheets on Excel?
Now we have divided the data into multiple worksheets.
#1 – DOJ Worksheet
#2 – Dept Worksheet
#3 – Salary Worksheet
#4 – Summary Worksheet
We have three pieces of information in three different worksheets, and for “Summary Sheet,” we need to fetch the data from three different worksheets.
- In Summary, Sheet, open the VLOOKUP function.
- Choose the LOOKUP value as the A3 cell.
- Since we are fetching the DOJ, for the selection of Table Array, go to the DOJ worksheet and choose the table range.
- Now mention the column number as 2 and range lookup as 0.
Now the result will be –
As you can see in the table array shows the worksheet name from where it is taking the reference of.
- Similarly, do the same thing got Dept & Salary details as well. For Dept, choose the table range from the “Dept” worksheet, and for Salary, choose table range from the “Salary” worksheet.
- Next, do the same thing for Salary as well.
Like this, we can fetch the data from different worksheets using the VLOOKUP function.
Things to Remember Here
- While fetching the data from different worksheets using the VLOOKUP function, we need to select the table array from the respective worksheet of the result looking column.
- The lookup value should always be to the left of the required result column.
- Always make the table array as an absolute referenceAbsolute ReferenceAbsolute reference in excel is a type of cell reference in which the cells being referred to do not change, as they did in relative reference. By pressing f4, we can create a formula for absolute referencing. by pressing the F4 function key.
This has been a guide to VLOOKUP on different sheets. Here we discuss how to fetch data from multiple excel sheets using the VLOOKUP function along with practical examples and downloadable excel templates. You may learn more about excel from the following articles –