Formula Errors

Fix Excel VLOOKUP Values That Disappear When a Source Workbook Closes

Jason MillerJason Miller Nov 18, 2026 869 views

Question details

The user needs external VLOOKUP formulas to return the correct values after the source workbook closes.

Fix Excel VLOOKUP Values That Disappear When a Source Workbook Closes
Product
Microsoft Excel
Device & OS
not provided
Scenario
A workbook uses VLOOKUP and IFERROR to retrieve data from another workbook.
Observed behavior
The formulas return values while the source workbook is open, but results appear blank or incorrect after it closes.
Before you start

Locate the source workbook and confirm that it has been saved in an accessible location. Identify one affected formula so you can inspect its external reference and underlying error.

Solution 1Recommended

Repair the external link and check the VLOOKUP formula

Use this solution to correct a broken source path, expose hidden errors, and recalculate the workbook.

1
Check the source link

In the workbook containing the formulas, open Data > Edit Links or Data > Workbook Links, depending on your Excel version. Select the source and update the link or change its source to the saved workbook if the path is incorrect.

2
Inspect the lookup range

Select an affected cell and check the formula bar. Confirm that VLOOKUP points to the correct source sheet and range, and use absolute references such as $A$2:$D$100 so the range does not shift when the formula is copied.

3
Expose the error

In the formula bar, temporarily remove the outer IFERROR wrapper and leave the VLOOKUP expression in place. Check the resulting error and lookup value; for an exact match, verify that the last VLOOKUP argument is FALSE and that the source contains a matching value.

4
Recalculate the workbook

Choose Formulas > Calculation Options > Automatic, then press Ctrl+Alt+F9 to force a full recalculation. Restore IFERROR after resolving the underlying issue, save the workbooks, and test the result with the source closed.

Repair the external link and check the VLOOKUP formula
IFERROR can hide the cause: If IFERROR is set to return an empty string, a lookup or reference error can appear as a blank cell.
Free Microsoft Office alternative

Work with Excel spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar interface for opening and editing spreadsheets. Its Microsoft Office format compatibility makes it easy to move existing files into WPS; check advanced formulas and links after migration.

Open and edit Microsoft Excel XLSX and XLS filesWork with VLOOKUP formulas in WPS SpreadsheetsUse a familiar spreadsheet interfaceMove existing Office files into a lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does my VLOOKUP show a blank instead of an error?

The formula may use IFERROR with an empty string as its fallback. Select the cell and temporarily remove the IFERROR wrapper in the formula bar to see the underlying error.

Where can I change the path to the source workbook?

Open Data > Edit Links or Data > Workbook Links, select the source workbook, and use the option to change the source. Choose the workbook at its current saved location.

Can VLOOKUP use data from a closed workbook?

Yes. Excel can use an external reference to a closed workbook, provided the reference and source are valid. Check the link path, lookup range, and calculation settings if the result is incorrect.

Will Power Query update the imported data automatically?

Imported data does not necessarily update whenever the source changes. Use Data > Refresh All to retrieve current data, and make sure the source workbook is accessible.