Function Problems

How to Fix XLOOKUP #REF! Error with External Tables in Excel Online

John WilsonJohn Wilson Oct 20, 2026 869 views

Question details

The user is encountering a #REF! error in Excel for the Web when using the XLOOKUP function to reference structured tables in an external workbook.

How to Fix XLOOKUP #REF! Error with External Tables in Excel for the Web
Product
Microsoft Excel for the Web
Device & OS
not provided
Scenario
Creating or editing an XLOOKUP formula online that targets structured table columns in an external Excel workbook stored on the same SharePoint site.
Observed behavior
Excel for the Web inserts absolute cell references instead of structured table references, resulting in a #REF! error when the formula is edited online, even though the same formula works perfectly in the desktop application.
Before you start

Verify that both workbooks are stored in the same SharePoint site or OneDrive directory, and ensure you have full edit permissions for both files before troubleshooting the formula.

Solution 1Recommended

Use Standard External Cell Ranges Instead of Structured Table References

Since Excel for the Web struggles to parse external structured table references dynamically, replacing them with standard absolute cell ranges bypasses the reference error.

Excel for the Web currently has limitations when dealing with structured references (e.g., Table1[Column1]) across different workbooks. Standardizing your range references manually ensures the web application can read the data correctly.

1
Open the Workbook Online

Launch your primary Excel workbook in Excel for the Web where the #REF! error is occurring.

2
Locate the Formula

Select the cell containing the broken XLOOKUP formula.

3
Rewrite Using Absolute Ranges

Modify the formula to use standard range references (e.g., '[ExternalWorkbook.xlsx]Sheet1!$A$2:$A$100') instead of the structured table names.

4
Apply and Verify

Press Enter to apply the updated formula and verify that the correct values are returned from the external workbook.

Use Standard External Cell Ranges Instead of Structured Table References
Compatibility Tip: Using standard absolute cell references ensures maximum compatibility whether you are viewing the file in Excel for the Web or the desktop application.
Free Microsoft Office alternative

Use WPS Office for Reliable Cross-Workbook References

If you frequently encounter formula limitations and broken external links in web-based spreadsheet editors, consider switching to WPS Office. It provides a robust, lightweight desktop experience capable of handling advanced functions like XLOOKUP and external workbook connections seamlessly, without the unexpected reference errors found in online environments.

  1. 1. Install WPS Office: Download and install the free WPS Office suite from the official website to your computer.
  2. 2. Open Your Linked Files: Launch WPS Spreadsheet and simultaneously open the primary and external .xlsx workbooks.
  3. 3. Apply XLOOKUP Seamlessly: Enter your XLOOKUP formulas utilizing robust local external references without worrying about web-based syntax limitations.
Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv)Robust offline support for XLOOKUP and complex external workbook referencesLightweight desktop application that prevents web-browser parsing errorsFamiliar user interface allowing for an immediate, seamless migration
microsoft office alternative - wps office

Frequently Asked Questions

Why does XLOOKUP work in Excel Desktop but return #REF! in Excel for the Web?

Excel for the Web has inherent limitations in dynamically parsing structured table references (like Table1[Column1]) across external workbooks. The web version often requires standard absolute cell ranges to evaluate cross-workbook links correctly.

Can I link two workbooks stored on different SharePoint sites using XLOOKUP?

Cross-site linking often introduces authentication and permission barriers in Excel for the Web, which frequently results in #REF! errors. It is highly recommended to keep linked workbooks in the exact same SharePoint directory or OneDrive folder.

Will the #REF! error disappear if I download both files locally?

Yes. Downloading both files to your local hard drive and opening them in a desktop spreadsheet application like Microsoft Excel or WPS Office will typically resolve external reference errors caused by web-editor limitations.