Function Problems

Compare Two Excel Rows and Return TRUE When All Cells Match

Maira MehtabMaira Mehtab Nov 18, 2026 868 views

Question details

Compare corresponding cells in two Excel rows and return TRUE only if every comparison matches.

Compare Two Excel Rows and Return TRUE When All Cells Match
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.
Before you start

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.

Solution 1Recommended

Use AND to Return TRUE or FALSE for Two Rows

Use this formula to check all corresponding cells and return one logical result.

1
Select a result cell

In Excel, click an empty cell outside the ranges you want to compare.

2
Enter the comparison formula

Type =AND(B1:K1=B2:K2), replacing both ranges with your own equally sized row ranges.

3
Confirm the formula

Press Enter in modern Excel. In older versions, press Ctrl+Shift+Enter if an array formula is required.

Use AND to Return TRUE or FALSE for Two Rows
How the result works: The formula returns TRUE only when every corresponding comparison is TRUE. Errors in the compared cells can cause an error result.
Compare rows in WPS Spreadsheets

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. 1. Open the workbook: Open WPS Spreadsheets and select File > Open to choose your workbook.
  2. 2. Choose a result cell: Click an empty cell outside the two row ranges you want to compare.
  3. 3. Enter the formula: Type =SUMPRODUCT(--(B1:K1<>B2:K2))=0, replacing the ranges with your own equally sized ranges.
  4. 4. Check the result: Press Enter. TRUE means no corresponding cells differ; FALSE means at least one pair differs.
Check an entire pair of row ranges with one formula.Open and edit Microsoft Excel XLSX and XLS files.Use a familiar spreadsheet interface with free tools.
microsoft office alternative - wps office

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.