Formula Errors

Compare Excel Workbooks and Fix VLOOKUP Spill Errors

Olivia MillerOlivia Miller Nov 18, 2026 869 views

Question details

Compare records across workbooks with different headers and troubleshoot VLOOKUP spill errors.

Compare Excel Workbooks and Fix VLOOKUP Spill Errors
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user needs to match corresponding fields across multiple workbooks and identify records without a match.
Observed behavior
The headers differ between workbooks, and a VLOOKUP formula returns a #SPILL! error.
Before you start

Open the workbooks you want to compare and identify a common key, such as an employee ID or invoice number, even if its column header differs. Check that the keys use consistent formats and that the comparison ranges cover the relevant rows.

Solution 1Recommended

Flag Unmatched Records Across Excel Workbooks

Use a helper column to compare a common key when the workbooks have different headers or column positions.

1
Identify the matching columns

Open both workbooks and locate the columns containing the same identifier. For example, column A in the current workbook and column B in Other.xlsx may both contain customer IDs, even if their headers differ.

2
Enter a comparison formula

In an empty helper column on row 2, enter =IF(A2="","",IF(COUNTIF('[Other.xlsx]Sheet1'!$B$2:$B$1000,A2)=0,"Unmatched","Matched")). Replace the workbook name, sheet name, key column, and row limits with your own.

3
Copy and filter the results

Drag the formula's fill handle down through your records. Select the helper column and use Data > Filter to show rows marked Unmatched.

4
Compare additional workbooks

Add a separate helper column for each additional workbook, updating the external workbook and range in the COUNTIF formula. Repeat the comparison in the other direction to find records missing from the first workbook.

Flag Unmatched Records Across Excel Workbooks
Check duplicate keys: This formula checks whether a key exists; it does not establish a one-to-one match or compare the other fields in a record. Keep the source workbooks open while using the external COUNTIF references.
Compare workbooks with WPS

Compare Workbook Records in WPS Spreadsheets

Use WPS Spreadsheets to open your workbooks, compare corresponding key columns, and flag unmatched records with a helper formula. For an Excel-specific #SPILL! error, inspect the formula in Excel as well.

  1. 1. Open the workbooks: In WPS Spreadsheets, use File > Open to open the workbooks you want to compare.
  2. 2. Find the common key: Locate the identifier column in each workbook, even if the headers or column positions differ.
  3. 3. Add a comparison formula: In an empty helper cell, enter a COUNTIF formula that checks the current row's key against the other workbook's key range. Select the source range in the open workbook to insert its reference.
  4. 4. Review unmatched rows: Fill the formula down, then use Data > Filter on the helper column to review keys with a count of zero. Repeat with additional workbooks as needed.
Flag unmatched keys across workbooks with COUNTIFOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interfaceFilter comparison results for review
QA img-9

Frequently Asked Questions

Can I compare columns with different headers?

Yes. Identify columns containing the same key and reference those ranges in a helper formula. The headers do not need to match, but the key values and formats should be consistent.

Why does a VLOOKUP formula show #SPILL!?

A VLOOKUP using an array of lookup values can return multiple results. Excel may show #SPILL! if those results are blocked, extend beyond the worksheet edge, or cannot spill from the formula's location. Select the error cell and its warning icon to check the cause.

How do I find records missing from either workbook?

Use a COUNTIF helper formula in the first workbook to check its keys against the second. Then reverse the comparison in the second workbook to find keys missing from the first.

Does a matched key mean the whole record is identical?

No. COUNTIF only confirms that the key exists in the comparison range. Compare the relevant fields separately and investigate duplicate keys if records must match one-to-one.