How to Fix XLOOKUP #REF! Error with External Tables in Excel Online
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.

- 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.
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.
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.
Launch your primary Excel workbook in Excel for the Web where the #REF! error is occurring.
Select the cell containing the broken XLOOKUP formula.
Modify the formula to use standard range references (e.g., '[ExternalWorkbook.xlsx]Sheet1!$A$2:$A$100') instead of the structured table names.
Press Enter to apply the updated formula and verify that the correct values are returned from the external workbook.

Create and Edit the Formula in the Excel Desktop Application
The desktop version of Excel fully supports structured external table references for XLOOKUP, allowing you to bypass web-based limitations.
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. Install WPS Office: Download and install the free WPS Office suite from the official website to your computer.
- 2. Open Your Linked Files: Launch WPS Spreadsheet and simultaneously open the primary and external .xlsx workbooks.
- 3. Apply XLOOKUP Seamlessly: Enter your XLOOKUP formulas utilizing robust local external references without worrying about web-based syntax limitations.

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.




