The VLOOKUP function is one of Excel’s most popular tools for retrieving data from tables. While it typically works within a single table or sheet, there are ways to extend its capabilities to lookup values across multiple files.

Consolidating data from multiple Excel files

To consolidate data from multiple Excel files, you can use the Consolidate function in Excel.

1. In the new Excel file, go to the Data tab and click the Consolidate button.
2. In the Consolidate dialog box, choose a function (e.g., SUM, AVERAGE, etc.) to apply to your data.
3. In the Reference field, select the ranges from different files that you want to consolidate. If your ranges are in separate files, you’ll need to open each file and select the appropriate range.

Now that your data is consolidated, you can use the VLOOKUP function to retrieve information from this combined dataset.

Using the VLOOKUP function to retrieve values from a consolidated Excel file

Once you have consolidated the data from all of the Excel files into a single Excel file, you can use the VLOOKUP function to lookup values in the consolidated Excel file.

To use the VLOOKUP function to lookup values in a consolidated Excel file.

For example, the following formula would lookup the value in cell A2 in the column B of the table_array:

=VLOOKUP(A2, table_array, 2)

If the table_array is in another Excel file, you need to specify the name of the Excel file and the worksheet name in the table_array argument.

For example, the following formula would lookup the value in cell A2 in the column B of the table_array in the worksheet Sheet2 of the Excel file Example_Name.xls:

=VLOOKUP(A2, [Example_Name.xls]Sheet2!A1:B200, 2)

Combining VLOOKUP with INDIRECT Function

The INDIRECT function allows you to construct the file and sheet references as text strings, making VLOOKUP more dynamic.

For instance, if cell B1 contains the text string ‘[Book2.xlsx]Sheet2!A1:B200’, the formula =VLOOKUP($A$2, INDIRECT(B1), 2, FALSE) will perform the same lookup as the previous example, but now the file and sheet reference can be changed simply by changing the text in cell B1.

This approach is useful when the source file might vary. Note however that the source file must be open for INDIRECT to work. For looking up data in closed workbooks, consider using Power Query (Get & Transform Data).

Related posts:

If Function with multiple conditions How to Vlookup other Tab in Excel? How to VLOOKUP Another Column?