Formula Errors

How to Fix Excel INDIRECT with XLOOKUP Returning #REF! Error

Aamir Naveed Akram Sep 28, 2026 868 views

Question details

The user is attempting to dynamically reference a table in another open workbook using the INDIRECT function nested inside an XLOOKUP formula, but it results in a #REF! error.

How to Fix Excel INDIRECT with XLOOKUP Returning #REF! Error

Product: Excel
Device & OS: not provided
Scenario: Pulling data dynamically from an external workbook table using XLOOKUP combined with INDIRECT.
Observed behavior: The formula returns a #REF! error when INDIRECT is added, even though the standard XLOOKUP reference works perfectly on its own.

Before you start

Ensure that the external workbook you are referencing is currently open, as the INDIRECT function notoriously returns a #REF! error when pointing to closed workbooks. Additionally, verify that your dynamically constructed text string perfectly matches the external table name or range.

Solution 1Recommended

Use a SWITCH Formula Alternative

Bypass the limitations and syntax strictness of INDIRECT by explicitly defining your external table arrays using a SWITCH function.

The INDIRECT function can be highly volatile and fails easily when referencing complex external tables or dynamic arrays. Using the SWITCH function is a much safer, non-volatile workaround. It explicitly routes the lookup to the correct table based on a specific cell value (like a branch name) without needing to evaluate a text string into a reference.

Identify the dynamic variables

Determine the cell that dictates which external table to search (for example, cell A1 containing the branch name 'Branch_A').

Construct the SWITCH function

Start typing =SWITCH(A1, "Branch_A", XLOOKUP(lookup_value, '[Workbook]Sheet'!Branch_A_Col, '[Workbook]Sheet'!Branch_A_Return), "Branch_B", XLOOKUP(...)).

Apply and test the formula

Press Enter to calculate the formula. The SWITCH function will assess the value in A1 and execute the corresponding XLOOKUP directly, avoiding reference errors.

Performance Benefit: Unlike INDIRECT, SWITCH is not a volatile function. This means it won't recalculate every time any change is made in the workbook, significantly improving your spreadsheet's calculation speed.

Solution 2

Troubleshoot the Reference with Dummy Data

If you must use INDIRECT, isolate the specific syntax error by creating a sanitized test file.

Perform Complex Lookups Easily

Easily Manage Dynamic Lookups with WPS Spreadsheets

WPS Office provides a highly stable spreadsheet environment that flawlessly handles advanced formulas. You can seamlessly utilize XLOOKUP, SWITCH, and INDIRECT functions without unexpected external reference errors, making complex data analysis smooth and efficient.

1. Open your workbooks: Launch WPS Spreadsheets and open both your main analytical workbook and the external source data workbook.
2. Start your formula: Select your target cell and type =SWITCH( to begin mapping your dynamic references.
3. Insert XLOOKUP securely: Embed your XLOOKUP formulas inside the SWITCH function to directly select ranges from the external workbook.
4. Calculate seamlessly: Press Enter to instantly pull your data across workbooks with high accuracy and zero #REF! errors.

Fully compatible with Microsoft Excel formulas and .xlsx formatsRobust and stable handling of external workbook linksBuilt-in support for advanced modern functions like XLOOKUP and SWITCHLightweight application that processes large datasets smoothly without lagging

Frequently Asked Questions

Why does the Excel INDIRECT function return a #REF! error with external links?

The most common reason for a #REF! error with INDIRECT is that the external workbook being referenced is closed. INDIRECT requires the source workbook to be open in the background to evaluate successfully. It can also occur if the text string passed to INDIRECT contains a typo or misses required single quotes around sheet names with spaces.

Can I use XLOOKUP to pull data from a closed workbook?

Yes, XLOOKUP natively supports pulling data from closed workbooks without returning an error. However, if you nest an INDIRECT function inside that XLOOKUP to determine the range dynamically, the external workbook must remain open due to the strict limitations of INDIRECT.

What is the best alternative to INDIRECT for dynamic formulas?

Functions like SWITCH, CHOOSE, and INDEX are excellent alternatives to INDIRECT. They allow you to dynamically select which array or table to look at without converting text strings into references, making your formulas faster, more stable, and less prone to #REF! errors.

Excel & Data Problems