Excel Match Column B to Column A Without a #SPILL! Error
Question details
The user needs to compare values in Column B with Column A and return a match result without triggering 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.
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.
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.
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.
Select cell C2 and enter =LET(values,FILTER(B2:B10000,B2:B10000<>""),IF(ISNUMBER(XMATCH(values,A:A)),"Match","No Match"))
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.
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.
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. Open the worksheet: Launch WPS Office, open your spreadsheet in WPS Spreadsheets, and locate Columns A, B, and the empty result column C.
- 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. 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. 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.

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.




