Formula Errors

How to Fix Excel INDIRECT Table Reference #REF! Errors

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to resolve a #REF! error that occurs when using the INDIRECT function to dynamically construct structured table references within an XLOOKUP formula.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Attempting to use XLOOKUP with dynamic table arrays generated by the INDIRECT function.
Observed behavior: The XLOOKUP formula evaluates correctly when the structured reference is entered directly, but returns a #REF! error when the exact same reference is constructed dynamically using INDIRECT.

Before you start

Verify that the referenced tables and column headers contain no trailing spaces or special characters, as INDIRECT requires a perfect text string match to resolve structured references successfully.

Solution 1Recommended

Replace INDIRECT with the SWITCH Function

Since INDIRECT can be fragile when handling structured table references, using the SWITCH function is a more robust, non-volatile alternative for dynamically choosing between different tables.

Using INDIRECT forces Excel to calculate the formula every time any change is made, which slows down the workbook. The SWITCH function allows you to explicitly state the possible table references without converting them to text strings first.

Identify the possible dynamic references

Determine all the potential table names or columns your XLOOKUP formula needs to switch between based on your criteria cell.

Restructure using SWITCH

Replace the INDIRECT portion of your formula with a SWITCH function. For example, instead of INDIRECT("Table" & A1 & "[Data]"), use SWITCH(A1, 1, Table1[Data], 2, Table2[Data]).

Nest inside XLOOKUP

Wrap your new SWITCH logic inside your XLOOKUP's return array or lookup array arguments, then press Enter to apply.

Performance Boost: Replacing INDIRECT with SWITCH or CHOOSE not only prevents text-parsing #REF! errors but also significantly improves workbook calculation speed.

Solution 2

Validate the Text String Passed to INDIRECT

If you must use the INDIRECT function, you need to ensure the formula generates the exact syntax required for Excel structured references.

Advanced Formulas in WPS Spreadsheet

Resolve Complex Formula Errors Easily with WPS Spreadsheet

WPS Office Spreadsheet provides robust support for modern functions like XLOOKUP, SWITCH, and INDIRECT, making it easy to manage dynamic table references without frustrating #REF! errors.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing the #REF! errors.
2. Locate the broken formula: Select the cell with the error and click into the formula bar.
3. Update to a robust formula: Swap the volatile INDIRECT function for a direct XLOOKUP or SWITCH function to ensure stable table references.
4. Apply and calculate: Press Enter to evaluate the updated formula instantly and verify the data returns correctly.

Fully compatible with Microsoft Excel formulas and structured table referencesBuilt-in error checking mechanisms for faster troubleshootingHigh performance and lightweight, avoiding calculation lag even with complex dynamic arraysFree, comprehensive alternative to Microsoft Office

microsoft office alternative - wps office

Frequently Asked Questions

Why does INDIRECT return a #REF! error with structured references?

A #REF! error with INDIRECT usually means the text string generated does not exactly match a valid Excel table name or column syntax, or you are trying to reference a table in an external workbook that is currently closed.

Can I use XLOOKUP with INDIRECT for dynamic column returns?

Yes, you can place INDIRECT inside the return_array argument of XLOOKUP. However, you must carefully format the text string to include the exact table name and brackets, e.g., INDIRECT("Table1[" & A2 & "]").

Are there better alternatives to INDIRECT in Excel?

Yes. Functions like SWITCH, CHOOSE, and INDEX are highly recommended over INDIRECT. They are non-volatile (meaning they don't slow down your workbook's performance) and handle structured table references directly without requiring exact text string matches.

Excel & Data Problems