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])
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:
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.
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:
Figure 3. End result: Consolidated summary from 3 different worksheets
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:
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: