Fix Excel VLOOKUP Values That Disappear When a Source Workbook Closes
Question details
The user needs external VLOOKUP formulas to return the correct values after the 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.
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.
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.
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.
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.
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.
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.

Import the source data with Power Query
Use a local copy of the source data when the external workbook link is unreliable.
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.

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.




