Use INDEX and MATCH with Another Excel Workbook
Question details
Use INDEX and MATCH to look up data in another workbook while reducing slow or unreliable external references.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A lookup formula needs to use data stored in a separate workbook, potentially on a network share, SharePoint, or OneDrive.
- Observed behavior
- External workbook references can calculate slowly or produce reference errors when the source file or its path is unavailable.
Confirm that you can open the source workbook and identify its worksheet, lookup column, and return column. If the file is on a shared location, check that you have permission to access it.
Import the source data and look it up locally
Use an imported table when external workbook lookups are slow or unreliable.
Open the Excel workbook where you want to display the lookup results.
Select Data > Get Data > From File > From Workbook, then choose the source file.
In the Navigator, select the source sheet or table and click Load to add it to the current workbook.
In a result cell, enter an INDEX/MATCH formula that points to the imported columns. For example, use =INDEX(Imported!$B$2:$B$100,MATCH(A2,Imported!$A$2:$A$100,0)), replacing the sheet name and ranges with your own.
Select Data > Refresh All to update the imported data from the source workbook.

Correct the external INDEX and MATCH references
Use a direct link when the source workbook is accessible and you want to reference it without importing the data.
Use INDEX and MATCH across workbooks in WPS Spreadsheet
Open both files in WPS Spreadsheet and create an INDEX/MATCH formula that references the source workbook. Keep the source file accessible for the external link.
- 1. Open both files: Launch WPS Spreadsheet and open the source and destination workbooks.
- 2. Select a result cell: In the destination workbook, click the cell where the matching result should appear.
- 3. Enter the lookup formula: Enter an INDEX/MATCH formula using the source workbook and worksheet references, such as =INDEX('[Source.xlsx]Sheet1'!$B$2:$B$100,MATCH(A2,'[Source.xlsx]Sheet1'!$A$2:$A$100,0)). Replace the example names and ranges with your own.
- 4. Save and verify: Press Enter, check the result, and use File > Save As to save the destination workbook in XLSX format.

Frequently Asked Questions
Can INDEX and MATCH reference a closed Excel workbook?
Yes. Create the external formula while both workbooks are open; Excel may include the full source path when the source workbook is closed. The source location must remain accessible for link updates.
Why is my external lookup slow?
Repeated calculations against a workbook on a network share or cloud location can be slow. Try Data > Get Data > From File > From Workbook, then run the lookup against the imported data.
How do I update imported source data?
In the destination workbook, select Data > Refresh All. Excel needs access to the source file to retrieve changes.
What does #N/A mean in an INDEX and MATCH formula?
It often means MATCH could not find the lookup value. Check the value and lookup range, and use 0 as the MATCH argument for an exact match.




