Excel Error Codes

Excel Match Column B to Column A Without a #SPILL! Error

John WilsonJohn Wilson Nov 12, 2026 868 views

Question details

The user needs to compare values in Column B with Column A and return a match result without triggering a #SPILL! error.

Excel Match Column B to Column A Without a #SPILL! Error
Product
Microsoft Excel dynamic-array formulas
Device & OS
not provided
Scenario
A formula is entered in C2 to identify whether each nonblank value in Column B also appears in Column A.
Observed behavior
The formula may return #SPILL! when cells below the formula contain data or when an entire-column reference creates an oversized output range.
Before you start

Check the cells below C2 and clear any values, formulas, or spaces in the intended spill range. Confirm that your data fits within a practical range such as B2:B10000 instead of using the entire Column B.

Solution 1Recommended

Use a limited dynamic formula to match Column B against Column A

Use this solution when you want one automatically expanding Match or No Match result for each nonblank value in Column B.

The formula filters out blank cells before checking each remaining value against Column A. Limiting the source range reduces unnecessary array output and helps prevent #SPILL! errors caused by oversized results.

The spill range must remain clear because Excel and WPS Spreadsheets cannot place results over existing cell contents.

1
Clear the spill range

Select the cells below C2 in the expected result area and use Home > Clear > Clear Contents to remove values or formulas that could block the output.

2
Enter the matching formula

Select cell C2 and enter =LET(values,FILTER(B2:B10000,B2:B10000<>""),IF(ISNUMBER(XMATCH(values,A:A)),"Match","No Match"))

3
Confirm the formula

Press Enter and review the results that spill from C2. Each nonblank value from Column B should display Match when it exists in Column A, or No Match when it does not.

4
Check a remaining error

If #SPILL! still appears, select the warning icon next to C2 and inspect the indicated spill range for blocking cells, merged cells, or a table restriction.

Result: The formula returns one result for every nonblank value in B2:B10000 while avoiding the unnecessary full-column output created by an unrestricted array.

Compare Columns in WPS Spreadsheets Without Dynamic-Array Spill Errors

WPS Spreadsheets can compare nonblank values in Column B with Column A using the same LET, FILTER, and XMATCH approach. Clear the destination range first, enter the formula in C2, and use the resulting spilled Match or No Match values to review your data.

  1. 1. Open the worksheet: Launch WPS Office, open your spreadsheet in WPS Spreadsheets, and locate Columns A, B, and the empty result column C.
  2. 2. Prepare the output range: Select the cells below C2 and choose Home > Clear > Clear Contents so the dynamic results have room to spill.
  3. 3. Enter the formula: Click C2 and enter =LET(values,FILTER(B2:B10000,B2:B10000<>""),IF(ISNUMBER(XMATCH(values,A:A)),"Match","No Match")), then press Enter.
  4. 4. Review and save the results: Check the spilled Match or No Match results, resolve any warning shown beside C2, and use File > Save As to save the workbook as an XLSX file if needed.
Reduce #SPILL! errors by limiting dynamic-array output to the actual data range.Automatically return Match or No Match results for nonblank values in Column B.Use familiar spreadsheet formulas and menu controls with a lightweight interface.Open, edit, and save Microsoft Excel XLSX and XLS files with format compatibility.
QA img-9

Frequently Asked Questions

Why does the formula return #SPILL! in C2?

Excel or WPS Spreadsheets cannot place the returned array because cells in the spill range contain data, formulas, or sometimes merged cells. Clear the cells below C2 and try again.

Can I use an entire column for the XMATCH lookup?

Yes. The lookup array A:A can remain an entire column, but limiting the source values in Column B, such as B2:B10000, prevents the formula from generating an unnecessarily large output array.

What formula matches every nonblank value in Column B to Column A?

Enter =LET(values,FILTER(B2:B10000,B2:B10000<>""),IF(ISNUMBER(XMATCH(values,A:A)),"Match","No Match")) in C2. It spills one result for each nonblank value in Column B.

How can I identify duplicates in both columns?

Add a separate result column and use COUNTIF or XMATCH in the opposite direction. For example, you can test values from Column A against Column B and return Match or No Match.

Will this dynamic formula work in WPS Spreadsheets?

Use a current WPS Spreadsheets version that supports LET, FILTER, and XMATCH. If a function is unavailable, use a compatible COUNTIF formula or update WPS Office.