Fix Excel VLOOKUP #N/A When a Source XLSB Is Closed
Question details
Diagnose why some VLOOKUP results return #N/A when a linked XLSB source workbook is closed.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A workbook uses VLOOKUP to retrieve values from a linked XLSB file.
- Observed behavior
- Some rows return #N/A while the source workbook is closed, but return the expected values after it is opened.
Open both workbooks and identify a row that changes from #N/A to a valid result. Note its formula, lookup value, and the location of the source XLSB file.
Use a Direct External Reference and Exact Matching
Use this approach when the lookup formula constructs its source range dynamically or may be using the wrong match mode.
Select an affected result cell and check the formula bar for INDIRECT or another function that constructs the source reference. INDIRECT does not resolve references to a closed external workbook.
Replace the constructed reference with a direct workbook and sheet reference. For example, while the source is open, enter =VLOOKUP(A2,'[Source.xlsb]Sheet1'!$A:$D,4,FALSE), adjusting the workbook, sheet, range, and return column to match your data.
Compare the value in A2 with the matching value in the first column of the source range. Correct numbers stored as text and remove extra or hidden spaces where needed.
Save the source XLSB, close it, and recalculate the lookup workbook using Formulas > Calculate Now. Confirm that the affected rows still return the expected values.

Check External Links and Calculation Settings
Use these checks if a direct VLOOKUP reference still gives unexpected results after the source workbook is closed.
Work with Lookup Formulas in WPS Spreadsheet
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface for opening and editing Office files. It can help you migrate everyday spreadsheet work, although it does not fix Excel's external-link behavior.

Frequently Asked Questions
Can VLOOKUP refer directly to a closed XLSB workbook?
Yes. Excel can use a direct external reference to a closed workbook, provided the source file is accessible and the link is valid. Check the formula bar and Data > Workbook Links or Edit Links for the source reference.
Why does INDIRECT stop working when the source workbook closes?
INDIRECT does not resolve references to a closed external workbook. Replace it with a direct reference to the workbook, sheet, and lookup range where possible.
Why do only some lookup rows return #N/A?
The affected lookup values may differ from the source values, for example because of extra spaces or numbers stored as text. Compare an affected lookup cell with the first column of the source range and use FALSE for an exact match.
How do I update the link to a moved source file?
Open Data > Workbook Links or Data > Edit Links in Excel, select the source link, and use the available change-source option to locate the XLSB file in its new location.




