How to Use XLOOKUP with a Workbook Name Stored in a Cell
Question details
The user wants to construct an XLOOKUP formula that dynamically retrieves data from an external workbook by referencing a workbook name stored in a specific cell.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Looking up data across multiple workbooks where the target file name changes based on cell values, requiring a dynamic external reference within the formula.
- Observed behavior
- To achieve this, the INDIRECT function must be used to concatenate the file path, the source workbook must remain open, and cell references need to be locked using absolute references before copying the formula.
Ensure that the external source workbook containing your target data is currently open. The INDIRECT function requires the referenced workbook to be open to evaluate successfully; otherwise, it will return a #REF! error.
Combine XLOOKUP with the INDIRECT Function
Use the INDIRECT function to build a dynamic reference string that incorporates the cell value as the workbook name for your XLOOKUP formula.
The INDIRECT function converts a text string into a valid cell reference. By concatenating the workbook name from a cell (e.g., $D$2) with the worksheet name and cell ranges, you can dynamically look up data in external files.
When copying formulas across rows or columns, remember to use absolute references (dollar signs) to lock the cell containing the workbook name so it doesn't shift.
Type your target workbook name (e.g., May 2025 Metrics.xlsx) into a specific cell in your main file, such as D2.
Locate and open the external workbook that contains the data you are trying to retrieve. Both the main workbook and the source workbook must be open at the same time.
Select your destination cell and enter the formula: =XLOOKUP([@Name],INDIRECT("'["&$D$2&"]Individual Tech Summary'!$A:$A"),INDIRECT("'["&$D$2&"]Individual Tech Summary'!$D:$D"))
Replace [@Name] with your actual lookup value. Update 'Individual Tech Summary' to the exact name of the worksheet in the source file, and adjust the column arrays ($A:$A and $D:$D) to match your lookup and return arrays.
Ensure the cell containing the workbook name has dollar signs ($D$2). This absolute reference locks the cell so it won't change to D3 or D4 when you drag the formula down to other rows.
Easily Handle Complex Formulas with WPS Spreadsheet
WPS Spreadsheet fully supports advanced array functions like XLOOKUP and dynamic referencing tools like INDIRECT. You can seamlessly link multiple workbooks and perform dynamic data analysis just like you would in Microsoft Excel.
- 1. Download and Install WPS Office: Get the free WPS Office suite from the official website and install it on your device.
- 2. Open Your Workbooks: Launch WPS Spreadsheet and open both your main workbook and the external data source workbook using the convenient tabbed interface.
- 3. Apply the Formula: Select the target cell, input your XLOOKUP and INDIRECT combined formula, and press Enter.
- 4. Drag to Autofill: Click and hold the small square at the bottom-right of the cell, then drag down to apply the dynamic lookup formula to your entire column.
Frequently Asked Questions
Why does my INDIRECT formula return a #REF! error?
When using INDIRECT to reference another workbook, that source workbook must be open in the background. If the file is closed, the INDIRECT function cannot resolve the external link and will output a #REF! error.
How do I lock a cell reference so it doesn't change when copying?
You can lock a cell reference by making it absolute. Add a dollar sign before both the column letter and the row number (e.g., $D$2). You can also press the F4 key while your cursor is on the cell reference in the formula bar to automatically add the dollar signs.
Can I use Power Query instead of INDIRECT for external workbook references?
Yes, Power Query is highly recommended for pulling data from external workbooks. Unlike INDIRECT, Power Query does not require the source workbooks to be open, provides a much more stable connection, and avoids the performance slowdowns associated with volatile functions.
