Function Problems

Restore a Missing Excel Column from an Older Workbook

Phi Hung VoPhi Hung Vo Nov 20, 2026 869 views

Question details

Restore a missing column in a newer Excel workbook by matching names to records in an older workbook.

Restore a Missing Excel Column from 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.
Before you start

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.

Solution 1Recommended

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.

1
Open both workbooks

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.

2
Enter the lookup formula

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.

3
Fill the formula down

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.

4
Keep the restored values

Check the results, then, if you want to remove the formulas and external links, copy the filled cells and use Home > Paste > Values.

Use an exact-match lookup to restore the missing column
Check duplicate names: VLOOKUP returns the first matching name in the older range. If names are duplicated, check the results or use a unique identifier to match records.
Restore spreadsheet data

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. 1. Open both files: In WPS Spreadsheets, open the older and newer XLSX workbooks and identify their name and value columns.
  2. 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. 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. 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.
Match names across workbooks to restore a missing columnOpen and save Microsoft Excel XLSX filesFill a lookup formula down for an entire listUse WPS Office for free
microsoft office alternative - wps office

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.