Compare Two Excel Rows and Return TRUE When All Cells Match
Question details
Compare corresponding cells in two Excel rows and return TRUE only if every comparison matches.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user wants to compare two equally sized row ranges and receive a single logical or numeric result.
- Observed behavior
- The desired result is TRUE or 1 when all corresponding cells match, and FALSE or 0 when any pair differs.
Identify two ranges with the same number of columns, such as B1:K1 and B2:K2, and choose an empty cell for the result. Standard Excel equality comparisons are not case-sensitive and may treat blank cells and zeros as equal.
Use AND to Return TRUE or FALSE for Two Rows
Use this formula to check all corresponding cells and return one logical result.
In Excel, click an empty cell outside the ranges you want to compare.
Type =AND(B1:K1=B2:K2), replacing both ranges with your own equally sized row ranges.
Press Enter in modern Excel. In older versions, press Ctrl+Shift+Enter if an array formula is required.

Return 1 or 0 with PRODUCT
Use a numeric result when another calculation needs 1 for matching rows or 0 for a mismatch.
Compare Two Rows in WPS Spreadsheets
Use a SUMPRODUCT formula in WPS Spreadsheets to check two rows and return TRUE only when no corresponding cells differ.
- 1. Open the workbook: Open WPS Spreadsheets and select File > Open to choose your workbook.
- 2. Choose a result cell: Click an empty cell outside the two row ranges you want to compare.
- 3. Enter the formula: Type =SUMPRODUCT(--(B1:K1<>B2:K2))=0, replacing the ranges with your own equally sized ranges.
- 4. Check the result: Press Enter. TRUE means no corresponding cells differ; FALSE means at least one pair differs.

Frequently Asked Questions
How do I return 1 instead of TRUE in Excel?
Enter =PRODUCT(--(B1:K1=B2:K2)) in an empty cell. It returns 1 when all corresponding cells match and 0 when at least one pair differs.
Why does the formula need Ctrl+Shift+Enter?
Some older Excel versions require array formulas to be confirmed with Ctrl+Shift+Enter. Modern Excel generally lets you confirm the AND comparison by pressing Enter.
Does the row comparison distinguish uppercase from lowercase?
No. The standard equality comparison in =AND(B1:K1=B2:K2) is not case-sensitive. For case-sensitive comparisons, use Excel's EXACT function on corresponding cells.
What if the two ranges have different numbers of cells?
Adjust the formula so both ranges have the same dimensions, such as B1:K1 and B2:K2. Each position in the first range should correspond to one position in the second.




