VLOOKUP from Another Sheet / Workbook

Updated on January 2, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Vlookup is a function which can be used to reference columns from the same sheet or we can use it to refer it from another worksheet or from another workbook, the reference sheet is same as the reference cell but the table array and index number are chosen from a different workbook or different worksheet.

How to Vlookup from Another Sheet / Workbook?

We all know the basics of the VLOOKUP functionBasics Of The VLOOKUP FunctionThe 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 in excel. Probably for beginners level, you must have practiced the formula from the same sheet itself. Fetching the data from another worksheet or from another workbook is slightly different using the VLOOKUP function in excel.

Let us have a look at how to use VLOOKUP from another sheet and then how it can be used on another workbook.

#1 – VLOOKUP from Another Sheet but Same Workbook

Now copy the result table to another worksheet in the same workbook.

Vlookup From Anothersheet Example 2

In the Result, Sheet opens the VLOOKUP formula and select the lookup value as cell A2.

Vlookup From Anothersheet Example 2-1

Now the table array is on a different sheet. Select the Data Sheet.

Vlookup From Anothersheet Example 2-2

Now, look at the formula in the table array. It does not only contain table reference, but it contains the sheet name as well.

Vlookup From Anothersheet Example 2-3

Note: Here, we need not need to type the sheet name manually. As soon as the cell in the other worksheet is selected, it automatically shows you the sheet name along with the cell reference of that sheet.

After selecting the range in the different worksheets, lock the range by typing the F4 key.

Vlookup From Anothersheet Example 2-4

Now you need not go back to the actual worksheet where you are applying the formula rather here itself. You can finish the formula. Enter the column reference number and range lookup type.

Vlookup From Anothersheet Example 2-5

We got the results from the other worksheet.

Vlookup From Anothersheet Example 2-6

–>> 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 Function Video Explanation


#2 – VLOOKUP from Different Workbook

We have seen how to fetch the data from a different worksheet in the same workbook. Now we will see how to fetch the data from a different workbook.

I have two workbooks; one is Data Workbook & Result Workbook.

Example 3

From Data Workbook, I am fetching the data to Result Workbook.

Step 1: Open the VLOOKUP function in the Result workbook and select lookup value.

Example 3-1

Step 2: Now go to the main data workbook and select the table array.

Example 3-2

You can use Ctrl + Tab to switch between all the opened excel workbooks.

The table array not only contains table range rathbut it also, but it also contains Workbook Name, Worksheet Name, and data range in that workbook.

We need to lock the table array here. Excel itself locked the table array automatically.

Example 3-3

Mention column index number and range lookup to get the result.

Now close the main workbook and see the formula.

Vlookup From Anothersheet Example 3-4

It shows the path of the excel file we are referring to. It shows the complete file and subfile names.

We got the results.

Example 3-5

Things to Remember About Excel Vlookup from Another Sheet (Same or Different Workbook)

You can Download this Vlookup from Another Sheet or Workbook Excel template here – Vlookup from Another Sheet Excel Template.

This has been a guide to the VLOOKUP from Another Sheet or Workbook in Excel. Here we look at how to use VLOOKUP from a different sheet or workbook along with practical examples and downloadable excel templates. You may learn more about excel from the following articles –