How to Use INDEX to Look Up Data from Another Workbook in Excel for the Web
Question details
The user needs to use the INDEX function to look up data from an external workbook in Excel for the Web and wants to resolve performance issues and OLE errors when opening these large linked files in desktop Excel.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating cross-workbook formulas using lookup functions like INDEX when files are hosted online, while managing calculation performance on large datasets.
- Observed behavior
- The lookup works when both workbooks are in OneDrive, but handling large workbooks with extensive calculations causes desktop Excel to crash, run slowly, or display a "waiting on OLE" error message.
Ensure both the source and destination workbooks are saved and fully synced in the same OneDrive or SharePoint folder before attempting to establish external formula links.
Set Up Cross-Workbook References via OneDrive
Upload your files to OneDrive and use absolute references to securely link data across workbooks in Excel for the Web.
Excel for the Web supports external references for lookup functions like INDEX, provided the files are hosted in a shared cloud environment. You must input the workbook syntax manually.
Navigate to your OneDrive or SharePoint account and upload both the source data workbook and the destination workbook into the exact same folder.
Open the destination workbook in Excel for the Web. Type =INDEX( in the target cell, then manually reference the external workbook by wrapping its name in square brackets, followed by the sheet name and an absolute range (for example: ='[SourceData.xlsx]Sheet1'!$A$1:$C$100).
Keep both files in their original OneDrive location without renaming them so the external references remain intact and update automatically.
Fix Desktop Excel Performance and OLE Errors
If large linked workbooks cause desktop Excel to crash or display "waiting on OLE" warnings, adjust your calculation settings and DDE options.
Experience Seamless Cross-Workbook Data Lookups with WPS Office
Struggling with "waiting on OLE" errors or crashes when linking large workbooks in Microsoft Excel? WPS Office offers a lightweight, highly compatible alternative that processes large datasets and complex INDEX formulas smoothly without bogging down your system resources.
- 1. Download and Install: Get WPS Office for free from the official website and install it on your computer.
- 2. Open Your Workbooks: Open your existing .xlsx workbooks directly in WPS Spreadsheet; all your data and formatting remain intact.
- 3. Link Data Easily: Use the INDEX function to seamlessly reference data across multiple local or cloud-synced workbooks.
Frequently Asked Questions
Can I use VLOOKUP or XLOOKUP across workbooks in Excel for the Web?
Yes. Similar to the INDEX function, you can use VLOOKUP, XLOOKUP, and MATCH across different workbooks in Excel for the Web, provided both files are saved and synced in the same OneDrive or SharePoint location.
Why does my external workbook reference show a #REF! error?
A #REF! error usually occurs if the source workbook was deleted, renamed, moved to a different folder, or if the referenced range was deleted. Ensure both workbooks are in the same location and the file name in your formula perfectly matches the source.
What does the 'waiting on OLE' message mean in Excel?
The 'waiting on OLE' (Object Linking and Embedding) message means Excel is waiting for another application, or another background instance of Excel, to complete an action. This frequently happens with heavy cross-workbook calculations. Enabling 'Ignore other applications that use DDE' in Excel's Advanced options usually resolves the bottleneck.
