Formula Errors

Fix Excel VLOOKUP #N/A When a Source XLSB Is Closed

Algirdas JasaitisAlgirdas Jasaitis Nov 18, 2026 869 views

Question details

Diagnose why some VLOOKUP results return #N/A when a linked XLSB source workbook is closed.

Fix Excel VLOOKUP #N/A When a Source XLSB 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.
Before you start

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.

Solution 1Recommended

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.

1
Inspect the formula

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.

2
Reference the source directly

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.

3
Check the lookup values

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.

4
Test the closed workbook

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.

Use a Direct External Reference and Exact Matching
External file path: Excel may display the source workbook's full path in the formula after the source is closed. The source file must remain accessible for the link to update.
Free Microsoft Office alternative

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.

Create and review VLOOKUP formulas in a familiar spreadsheet interfaceOpen and edit Microsoft Excel XLS and XLSX filesUse a free, lightweight office suite for everyday spreadsheet tasksMove common spreadsheet workflows to WPS Office with a familiar interface
QA img-9

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.