Restore a Missing Excel Column from an Older Workbook
Question details
Restore a missing column in a newer Excel workbook by matching names to records in an older workbook.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The newer workbook has additional names and no longer contains a column available in the older workbook.
- Observed behavior
- The missing values need to be matched to the correct names across the two workbooks, while names absent from the older file should remain blank.
Open both workbooks and identify the name column and the column to restore in the older file. Check that names are spelled consistently and decide where the restored values should go in the newer file.
Use an exact-match lookup to restore the missing column
Use this method to retrieve values from the older workbook by name and leave unmatched names blank.
The example assumes the older file is named OldFile.xlsx, its sheet is named Sheet1, names are in A3:A6, and the values to restore are in B3:B6. In the newer workbook, names start in E3 and the restored values will go in column F.
Open OldFile.xlsx and the newer workbook in Excel. In the older workbook, confirm that the name column is to the left of the column containing the values to restore.
In the newer workbook, select F3 and enter =IF(COUNTIF('[OldFile.xlsx]Sheet1'!$A$3:$A$6,E3),VLOOKUP(E3,'[OldFile.xlsx]Sheet1'!$A$3:$B$6,2,FALSE),""). Replace the file name, sheet name, ranges, and E3 with your actual references.
Select F3 and drag its fill handle down alongside the names in column E. Matched names return the old value; names not found in the older list display a blank.
Check the results, then, if you want to remove the formulas and external links, copy the filled cells and use Home > Paste > Values.

Restore the missing column with WPS Spreadsheets
Open both workbooks in WPS Spreadsheets and use an exact-match lookup to retrieve the older values by name.
- 1. Open both files: In WPS Spreadsheets, open the older and newer XLSX workbooks and identify their name and value columns.
- 2. Select the destination cell: In the newer workbook, click the first cell where the missing value should appear, such as F3 next to the name in E3.
- 3. Enter the lookup: Enter =IF(COUNTIF('[OldFile.xlsx]Sheet1'!$A$3:$A$6,E3),VLOOKUP(E3,'[OldFile.xlsx]Sheet1'!$A$3:$B$6,2,FALSE),"") and adjust the workbook, sheet, and cell references to match your files.
- 4. Fill and check the results: Drag the formula cell's fill handle down the destination column, then check matched values and confirm that names absent from the older list are blank.

Frequently Asked Questions
Will the formula work across two separate Excel files?
Yes. Open both workbooks and include the older file and sheet in the formula reference, such as '[OldFile.xlsx]Sheet1'!$A$3:$B$6. Update the reference to match your file and sheet names.
What happens to names that only appear in the newer workbook?
The COUNTIF check finds no match, so the IF formula displays a blank for those names.
Why is a name that exists in both files not matching?
Check both name cells for spelling differences or extra spaces. The lookup requires matching text; correct the names and check the result again.
How do I remove the link to the older workbook after restoring the data?
Select and copy the restored cells in the newer workbook, then choose Home > Paste > Values. This replaces the lookup formulas with their current results.




