Using VLOOKUP Across Workbooks in Excel
The VLOOKUP function in Excel lets you search for a value in the first column of a table. It then returns a value from another column in the same row. When working with data across multiple Excel files, you might need to reference data from a different workbook. This guide explains how to use VLOOKUP across workbooks.
First, understand the syntax of the VLOOKUP function:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value: The value you want to find.
- table_array: The range of cells containing the data. This can be in another workbook.
- col_index_num: The column number from which to retrieve the value.
- range_lookup: Optional. Use FALSE for an exact match or TRUE for an approximate match.
To use VLOOKUP across workbooks, start by opening both the source workbook (with the data table) and the destination workbook (where you want the result). In the destination workbook, select the cell where you want the result. Begin typing the formula: =VLOOKUP(.
Next, enter the lookup value. Click on the cell that contains the value you want to search for, or type it directly.
Then, specify the table array. Type a comma to move to the next argument. Switch to the source workbook by clicking on it. Select the range of cells that contains the data table. Excel will automatically insert the workbook and sheet reference into the formula. It will look something like this:
‘[SourceWorkbook.xlsx]Sheet1’!$A$1:$B$100
After defining the table array, type another comma and enter the column index number. This is the number of the column in your table array from which you want to retrieve the data. For example, if you want data from the second column, enter 2.
Then, type another comma and specify the range lookup option. Enter FALSE for an exact match or TRUE for an approximate match. Close the parentheses to complete the formula. Press **Enter**. Excel will display the value from the other workbook based on your criteria.
For example, suppose you are looking up a product price using a product ID. The data table is in a workbook named ProductData.xlsx. Your formula might look like this:
=VLOOKUP(A2, ‘[ProductData.xlsx]Sheet1’!$A$2:$B$100, 2, FALSE)
In this formula:
- A2 is the cell containing the product ID.
- [ProductData.xlsx]Sheet1′!$A$2:$B$100 is the range in the source workbook.
- 2 indicates you want to retrieve the value from the second column.
- FALSE specifies an exact match.
Keep some important points in mind when using VLOOKUP across workbooks. Both workbooks must be open when you create the formula. This allows Excel to establish the correct path reference. Use absolute references (with dollar signs, like $A$2:$B$100) for the table array range. This prevents the range from changing if you copy the formula to other cells.
Ensure the source workbook is saved in a fixed location. Moving or renaming it will break the link in your formula. To handle situations where the lookup value is not found, use the IFERROR function. For example:
=IFERROR(VLOOKUP(A2, ‘[ProductData.xlsx]Sheet1’!$A$2:$B$100, 2, FALSE), “Not Found”)
This formula will display “Not Found” instead of an error message if the lookup value doesn’t exist.
