Compare Two Columns and Find Differences

Excel & Google Sheets

How it works

The formula checks whether the value in A2 equals the value in B2. If they're identical, it returns Match; otherwise it returns Mismatch. Copy it down the column and every row gets checked automatically. The comparison is not case-sensitive — "ana@co.com" and "ANA@co.com" count as a match. If you need to find values from column A that appear anywhere in column B (not just the same row), use =IF(COUNTIF(B:B,A2)>0,"In list","Missing") instead.

A2=B2

Compares the two cells in this row. Returns TRUE when they're equal.

"Match"

What to show when the comparison is TRUE.

"Mismatch"

What to show when the comparison is FALSE.

When to use it

Use this when reconciling two versions of the same list: exported data vs. a master file, last month's roster vs. this month's, or system A vs. system B.

Common mistakes

• ✕Comparing rows when you actually need to compare whole lists.

  ✓Row-by-row comparison breaks if the lists are sorted differently. Use =COUNTIF(B:B,A2)>0 to check if a value exists anywhere in the other column.

• ✕Hidden spaces make identical-looking values mismatch.

  ✓Wrap both sides in TRIM: =IF(TRIM(A2)=TRIM(B2),"Match","Mismatch").

• ✕Expecting a case-sensitive comparison.

  ✓The = operator ignores case. Use EXACT for a case-sensitive check: =IF(EXACT(A2,B2),"Match","Mismatch").