FLASH SALE! - "CHATGPT AND ARTIFICIAL INTELLIGENCE FOR MICROSOFT EXCEL AT 60% OFF" Enroll Now

VLOOKUP on Different Sheets

Updated on January 2, 2024
Article byWallstreetmojo Team
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

VLOOKUP from Different Sheets

In general, we will get data in multiple worksheets, and having all the information in single sheets is impossible in 99% of the cases. When the data is scattered to different worksheets, it isn’t easy to move between sheets and pull the data from other sheets. When removing the data, VLOOKUP is vital. As a new learner, you do not have to try to fetch the data from one worksheet to another. However, this article will show you how to apply VLOOKUP to fetch the data from different sheets.

Example of VLOOKUP Function on Different Sheets

Let us 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, etc., are unique identifiers. read more with different sheets in 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 the “Data Table”, we have all the values, and in the “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 the F3 cell.
VLOOKUP on Different Sheets Example 1.3.0
  • The 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 is the col_index_num. So, the table array column number will be the 2nd column, so mention the same.
Example 1.6.0
  • Next is range_lookup. We have two options: 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
  • We are done. Close the bracket and press the “Enter” key to get the result.
Example 1.8.0
  • We have “DOJ,” copy the formula and paste it to the next cell. This time we need not apply the formula again. We 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 all three required details in the single formula itself. Now, we will see how to apply VLOOKUP to fetch data from one worksheet to another.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

VLOOKUP Excel Video Explanation

 

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 the “Summary Sheet,” we need to fetch the data from three other worksheets.

  • In the “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 Table Array selection, go to the DOJ worksheet and choose the table range.
VLOOKUP on Different Sheets Example 1.18
  • Now, mention column number 2 and the range lookup 0.
Example 1.19

Now, the result will be: 

VLOOKUP on Different Sheets Example 1.20

As you can see, the table array shows the worksheet name from where it is taking the reference.

  • Similarly, do the same to get “Dept” and “Salary” details. For “Dept,” choose the table range from the “Dept” worksheet. For “Salary,” choose the 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

This article is a guide to VLOOKUP on different sheets. Here, we discuss how to fetch data from multiple Excel sheets using the VLOOKUP function, practical examples, and downloadable Excel templates. You may learn more about Excel from the following articles: –