< Go Back

VLOOKUP from another workbook

VLOOKUP from another workbook in Excel

VLOOKUP is a very useful function in Excel for retrieving data corresponding to search criteria. It is versatile and can be used to fetch data from the same worksheet, different worksheet or even an entirely different workbook. Let’s take a look at how to use VLOOKUP from another workbook in Excel.

VLOOKUP from another workbook: Generic Formula

=VLOOKUP(lookup_value, [workbook]sheet!table_array, col_index_number, [range_lookup])

Explanation

The generic VLOOKUP function requires a few parameters as explained below.

  • lookup_value: The value you want to search. It can be a number, text string or even a cell reference (search for a value that is stored in a certain cell)

  • table_array: The range of data you want to search within. You can add workbook and sheet name here to get data from.

  • col_index_number: Identifier for the column from which the value should be retrieved from

  • range_lookup [Optional]: This specifies whether you want to search for an approximate match (TRUE) or exact match (FALSE).

Using the VLOOKUP formula

The formula for VLOOKUP from another workbook is very similar to the generic VLOOKUP formula. Here’s an example:

=VLOOKUP(C4,'[products.xlsx]Products!B5:C9,2,0)

This formula reads as: Look for the value stored in cell C4 of the current worksheet, and use that value as a search criterion. Go to Excel file named “products.xlsx”, then go to the sheet named Products, and search within the data range B5-B9. Then return the value stored in the second column of the given range (B5:C9), which is column C.

Real-World Example: Product summary

VLOOKUP is rarely ever used on the same sheets. It is useful for creating summaries, and organizations often generate such reports on entirely separate workbooks. That makes VLOOKUP from another workbook very useful. Let’s take a look at how we can use the formula.

Here’s an example scenario, where we have a workbook called “products.xlsx” and it has different sheets.

  • The first sheet contains a list of products and their related information

Figure 1.1. Sample data: Product list

  • The second sheet contains a record of the quantity of each product available in the inventory against its product ID.

Figure 1.2. Sample data: Product inventory details

 

  • The third sheet contains a price list of all the products.

Figure 1.3. Sample data: Product price list

Note: The common identifier in all these sheets is ID, so we will use that for searching purposes.

Now, let’s create a new workbook, and build a summary from “products.xlsx”. It will look something like this:

Figure 1.4. Sample data: Summary template

We will start by getting the names of products from “products.xlsx”. For that, we need the following formula.

=VLOOKUP(B5,[products.xlsx]Products!B5:C9,2,0)

This formula will use the ID stored in cell B5 (current worksheet, in this case 112), then go to “products.xlsx”, open up the Products worksheet, search from B5 to B9 for the given ID, and if found, return the value available in column C.

Figure 2.1. Formula Result: Retrieve names using VLOOKUP from another workbook

Similarly, to retrieve quantity and price values, we will use the following formulas:

  • Quantity:

=VLOOKUP(B5,[products.xlsx]Inventory!B5:C9,2,0)

  • Price:

=VLOOKUP(B5,[products.xlsx]Prices!B5:C9,2,0)

Figure 3. End result: Consolidated summary from 3 different worksheets

Notes

If you’re just specifying the name of another workbook (products.xlsx in this case), make sure the file is present in the same directory as the workbook you’re currently working on. If the other workbook is located elsewhere, you need to provide the complete path in the following format:

=VLOOKUP(B5,'\path\to\file\[products.xlsx]Summary'!B5:C9,2,0)

Note that we added single quotes and included file path and workbook name within the quotes. The name of the worksheet is also to be included within these same quotes. Here’s an example:

=VLOOKUP(B5,'C:\Users\Me\Documents\records\[products.xlsx]Summary'!B5:C9,2,0)

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar