VLOOKUP on Different Sheets

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.read more with different sheets on excel.

You can download this VLOOKUP Different Sheets Excel Template here – VLOOKUP Different Sheets Excel Template
  • Look at the below data in excel.
VLOOKUP on Different Sheets Example 1.0.0

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.

VLOOKUP on Different Sheets Example 1.1

To fetch DOJ, open the VLOOKUP function in the G3 cell.

Example 1.1.0
  • The first argument is Lookup Value, so our lookup value is Employee Name, so select F3 cell.
VLOOKUP on Different Sheets Example 1.3.0
  • Table Array will be the“Data Table” range from A3 to D11.
Example 1.4.0
  • Once you have selected the Table Array, make it an absolute reference by pressing the F4 key.
VLOOKUP on Different Sheets Example 1.5.0
  • Next up is the col index num, so from the table array selection column number will be 2nd column, so mention the same.
Example 1.6.0
  • 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.
VLOOKUP on Different Sheets Example 1.7.0
  • Ok, we are done. Close the bracket and hit the enter key to get the result.
Example 1.8.0
  • 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.
VLOOKUP on Different Sheets Example 1.9.0
  • Similarly, for the “Salary” column, change the column number to 4.
Example 1.10.1

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

VLOOKUP on Different Sheets Example 1.12

#2 – Dept Worksheet

Example 1.13

#3 – Salary Worksheet

VLOOKUP on Different Sheets Example 1.14

#4 – Summary Worksheet

Example 1.15

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.
VLOOKUP on Different Sheets Example 1.16
  • Choose the LOOKUP value as the A3 cell.
Example 1.17
  • Since we are fetching the DOJ, for the selection of Table Array, go to the DOJ worksheet and choose the table range.
VLOOKUP on Different Sheets Example 1.18
  • Now mention the column number as 2 and range lookup as 0.
Example 1.19

Now the result will be –

VLOOKUP on Different Sheets Example 1.20

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.
Example 1.21
  • Next, do the same thing for Salary as well.
VLOOKUP on Different Sheets Example 1.22

Like this, we can fetch the data from different worksheets using the VLOOKUP function.

Things to Remember Here

Recommended Articles

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 –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>