Function Problems

How to Retrieve a Value from Another Excel Workbook by Row Number

Maira Mehtab Oct 1, 2026 869 views

Question details

The user needs to extract a specific value from an external Excel workbook based on a matched row number, while navigating the limitations of dynamic reference functions.

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Pulling data, such as an expiration date or company ID, from a separate external Excel file using a row number.
Observed behavior: Using the INDIRECT function successfully retrieves the value when the external workbook is open, but results in a #REF! error when the source workbook is closed.

Before you start

Ensure you have both the destination workbook and the source workbook saved on your local drive, and take note of the exact file path and sheet name of the external data source.

Solution 1Recommended

Use INDEX and MATCH for Reliable External Lookups

The INDEX and MATCH combination is the most reliable method for pulling data from external workbooks because it continues to function properly even when the source file is closed.

Dynamic referencing using INDIRECT requires the source file to be open in the background to calculate successfully. To bypass this limitation and prevent #REF! errors, you should use standard lookup functions.

Open both workbooks

Open your main working document and the external source file (e.g., 'Second File.xlsx') so Excel can automatically build the exact file path when you select cells.

Enter the INDEX formula

In your destination cell, type '=INDEX(' and then switch to your external workbook to select the column containing the values you want to retrieve.

Add the MATCH function

Continue the formula by typing ', MATCH(' and select your lookup value (like C2). Then, select the lookup column in the external workbook, followed by ', 0))' for an exact match.

Press Enter to execute

Press Enter. You can now safely close the external workbook. The formula will automatically update to show the full file path and continue displaying the correct value.

Use INDEX and MATCH for Reliable External Lookups

XLOOKUP Alternative: If you are using a modern version of Excel or WPS Office, you can replace INDEX/MATCH with a simpler XLOOKUP formula, which also natively supports closed external workbooks.

Solution 2

Use the INDIRECT Function (Only When Source is Open)

You can construct a cell reference dynamically using the row number via the INDIRECT function, provided the external workbook remains open during your session.

Advanced Spreadsheet Tool

Easily Manage External Data Links with WPS Spreadsheet

WPS Spreadsheet provides robust support for cross-workbook referencing, including advanced functions like XLOOKUP, INDEX, and MATCH. Handle complex data across multiple files seamlessly with our highly compatible suite.

1. Open files in WPS Spreadsheet: Launch WPS Office and open your main document along with the external data source using the convenient tabbed interface.
2. Insert the lookup function: Select the cell where you want to retrieve the value, then click the 'Insert Function' (fx) button next to the formula bar.
3. Build your cross-workbook reference: Choose XLOOKUP or INDEX to build your reference. Selecting cells across different tabs automatically generates the correct external path.
4. Save in standard formats: Save your finished file in .xlsx format to ensure the cross-workbook formulas work flawlessly for anyone you share it with.

100% compatible with Microsoft Excel formats (.xlsx, .xls, .csv)Fully supports advanced lookup functions like XLOOKUP and INDEX/MATCHSeamlessly manages external links and cross-workbook data without laggingFree, lightweight, and features a familiar tabbed interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does my INDIRECT formula return a #REF! error when I reference another file?

The INDIRECT function cannot process dynamic paths to closed workbooks. If you reference an external file using INDIRECT, that source file must remain actively open in the background; otherwise, the application cannot resolve the address and displays a #REF! error.

Can I use XLOOKUP across different workbooks?

Yes, XLOOKUP perfectly supports cross-workbook references. Unlike INDIRECT, formulas using XLOOKUP (or INDEX/MATCH) will successfully evaluate and retrieve matched data even if the external workbook is completely closed.

How do I easily find the correct file path syntax for my external workbook?

The easiest way is to open both workbooks side-by-side. In your destination file, type '=' and then click on any cell in the source file, and press Enter. Once you close the source file, Excel or WPS will automatically update that simple formula to display the full, correctly formatted file path, which you can then copy into your lookup formulas.

Excel & Data Problems