How to Use INDIRECT with XLOOKUP for External Workbooks
Question details
The user needs to use the INDIRECT function to build dynamic XLOOKUP references where the external workbook names, worksheet names, lookup ranges, and return ranges are stored as variable text in specific cells.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Building flexible data lookup formulas that pull information from various external workbooks without hardcoding the file paths or sheet names directly into the formula syntax.
- Observed behavior
- Constructing the correct text string syntax using ampersands and single quotes is complex, and the formula often returns a #REF! error if the external workbooks are closed.
Ensure all referenced external workbooks are currently open on your computer before writing or recalculating your formula. The INDIRECT function is volatile and cannot resolve links to closed workbooks, which will immediately result in a #REF! error.
Build a Dynamic XLOOKUP Reference with INDIRECT
Concatenate cell values to form valid external reference strings that INDIRECT can evaluate as lookup and return arguments for XLOOKUP.
When referencing external workbooks, the standard syntax requires single quotes around the path and sheet name, and square brackets around the workbook name (e.g., '[Data.xlsx]Sheet1'!A1:A10). You must recreate this exact string structure using cell references and the ampersand (&) concatenation operator so INDIRECT can read it.
Organize your variables in dedicated cells. For example: A11 holds the workbook name, B11 holds the sheet name, E11 holds the lookup range, F11 holds the return range, and I11 holds the lookup value.
Combine the variables and syntax characters using the ampersand operator to build the lookup array path: "'["&A11&"]"&B11&"'!"&E11.
Similarly, combine the variables to build the return array path: "'["&A11&"]"&B11&"'!"&F11.
Select your output cell and enter the combined formula: =XLOOKUP(I11, INDIRECT("'["&A11&"]"&B11&"'!"&E11), INDIRECT("'["&A11&"]"&B11&"'!"&F11)).
Press Enter to execute the function and retrieve the matched data from the external file.
Use Nested XLOOKUP for Dynamic Column Selection
Use a nested XLOOKUP inside your formula logic to dynamically identify the correct column or 2D range from an external file, and supply that result to INDIRECT.
Master Complex Formulas with WPS Spreadsheet
WPS Spreadsheet offers full support for advanced array functions like XLOOKUP and dynamic string referencing with INDIRECT. It is highly optimized to seamlessly process complex formulas across multiple open workbooks, saving you time and preventing frustrating syntax errors.
- 1. Open WPS Spreadsheet: Launch the WPS Spreadsheet application on your device.
- 2. Open all necessary workbooks: Open your master workbook along with all the external source workbooks you intend to reference.
- 3. Define reference variables: Type your target workbook names, sheet names, and range variables in dedicated cells.
- 4. Write the XLOOKUP and INDIRECT formula: Click the result cell, type =XLOOKUP(, and use INDIRECT to define the lookup and return arrays by joining your variables with the & operator.
- 5. Retrieve your data: Press Enter to instantly fetch and display the dynamically matched data.
Frequently Asked Questions
Why does my INDIRECT and XLOOKUP formula return a #REF! error?
This almost always happens because the external workbook referenced by the INDIRECT function is closed. INDIRECT requires all external source files to be actively open in the background to calculate properly. If they are closed, Excel and WPS Spreadsheet will both return a #REF! error.
Do I need to include the file extension in the workbook name cell?
Yes, if the external file is saved as an .xlsx, .xls, or .csv, the cell containing your workbook name variable must include that exact file extension. Otherwise, the INDIRECT string will not match the system's file name correctly.
Can I use VLOOKUP instead of XLOOKUP with INDIRECT?
Yes, you can easily substitute XLOOKUP with VLOOKUP. You would use INDIRECT to dynamically define the 'table_array' argument, ensuring you still strictly follow the '[Workbook.xlsx]SheetName'!Range text formatting rules.
Why does my INDIRECT string formula look like text instead of calculating?
If the cell displays the formula itself instead of the calculated result, the cell formatting may be set to 'Text'. Change the cell format to 'General', then double-click the cell and press Enter to force it to recalculate as a formula.
