Compare Excel Workbooks and Fix VLOOKUP Spill Errors
Question details
Compare records across workbooks with different headers and troubleshoot 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.
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.
Flag Unmatched Records Across Excel Workbooks
Use a helper column to compare a common key when the workbooks have different headers or column positions.
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.
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.
Drag the formula's fill handle down through your records. Select the helper column and use Data > Filter to show rows marked Unmatched.
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.

Correct the VLOOKUP Range and Clear the Spill Area
Use this solution when Excel displays #SPILL! for a VLOOKUP formula.
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. Open the workbooks: In WPS Spreadsheets, use File > Open to open the workbooks you want to compare.
- 2. Find the common key: Locate the identifier column in each workbook, even if the headers or column positions differ.
- 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. 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.

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.




