File Sharing Problems

Use INDEX and MATCH with Another Excel Workbook

Huda QurayshiHuda Qurayshi Nov 18, 2026 868 views

Question details

Use INDEX and MATCH to look up data in another workbook while reducing slow or unreliable external references.

Use INDEX and MATCH with Data from Another Excel Workbook
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.
Before you start

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.

Solution 1Recommended

Import the source data and look it up locally

Use an imported table when external workbook lookups are slow or unreliable.

1
Open the destination workbook

Open the Excel workbook where you want to display the lookup results.

2
Import the source workbook

Select Data > Get Data > From File > From Workbook, then choose the source file.

3
Load the source data

In the Navigator, select the source sheet or table and click Load to add it to the current workbook.

4
Create the lookup

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.

5
Refresh when data changes

Select Data > Refresh All to update the imported data from the source workbook.

Import the source data and look it up locally
Keep the source accessible: The imported data remains in the destination workbook, but refreshing it still requires access to the source file.
Work with spreadsheet lookups

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. 1. Open both files: Launch WPS Spreadsheet and open the source and destination workbooks.
  2. 2. Select a result cell: In the destination workbook, click the cell where the matching result should appear.
  3. 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. 4. Save and verify: Press Enter, check the result, and use File > Save As to save the destination workbook in XLSX format.
Look up values across workbook ranges with INDEX and MATCHOpen and save Microsoft Excel XLSX filesWork in a familiar spreadsheet interfaceUse WPS Office for free
microsoft office alternative - wps office

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.