How to Compare Two Columns in Google Sheets
"Compare two columns" means different things in different contexts. Row-by-row equality? Finding values in A that aren't in B? Matching keys and returning related data? Each has a different technique. This guide covers all four common scenarios.
⚡ Quick Answer
For row-by-row: =A1=B1 or Conditional formatting. For "does A1 exist anywhere in B": =COUNTIF(B:B, A1)>0. For matching and returning data: =XLOOKUP(A1, D:D, E:E).
Scenario 1: Row-by-row comparison
Simple equality
=A1=B1Returns TRUE if identical, FALSE if different. Copy down column C to check every row. Case-insensitive.
Labeled comparison
=IF(A1=B1, "Match", "Different")Case-sensitive comparison
=EXACT(A1, B1)Returns TRUE only if identical including case. "Apple" ≠ "apple".
Highlight differences with conditional formatting
Select columns A and B
Highlight both columns.
Format → Conditional formatting
Panel opens on the right.
Custom formula rule
Under "Format cells if...", choose Custom formula is. Enter:
=$A1<>$B1Set formatting and click Done
Pick a fill color. Rows where A ≠ B highlight.
Scenario 2: Values in A not in B
COUNTIF existence check
=IF(COUNTIF(B:B, A1)=0, "Not in B", "In B")Returns "Not in B" for values missing from column B.
Highlight missing values
Select column A
Highlight the column to check.
Format → Conditional formatting → Custom formula
Enter:
=COUNTIF($B:$B, A1)=0Set fill color and Done
Red fill for missing. Values in A not anywhere in B are now highlighted.
Get the missing values as a list
=FILTER(A2:A100, COUNTIF(B2:B100, A2:A100)=0)Returns all values from A that don't exist in B, as a spilled array.
Scenario 3: Match keys, return related data
XLOOKUP (modern)
=XLOOKUP(A1, D:D, E:E, "Not found")Looks up A1 in column D, returns the matching value from column E. Fourth argument is what to show if no match.
VLOOKUP (classic)
=VLOOKUP(A1, D:E, 2, FALSE)Looks up A1 in the first column of D:E, returns column 2 (E). FALSE means exact match. See our VLOOKUP guide.
INDEX/MATCH
=INDEX(E:E, MATCH(A1, D:D, 0))More flexible than VLOOKUP; works when lookup column isn't leftmost.
Scenario 4: Find duplicates across two columns
COUNTIF to flag matches
=IF(COUNTIF(B:B, A1)>0, "In both", "Only in A")Highlight duplicates with conditional formatting
Select both columns
Highlight A and B together.
Format → Conditional formatting → Custom formula
Enter:
=COUNTIF($A:$B, A1)>1Set fill and Done
Every duplicate across both columns is highlighted.
Advanced: QUERY for complex comparisons
QUERY combines filtering, comparing, and returning specific columns in one formula.
=QUERY({A2:A100, B2:B100}, "SELECT Col1 WHERE Col1 <> Col2")Returns values from A where A ≠ B (row-by-row). See our QUERY complete guide for details.
Comparison of approaches
| Scenario | Best method |
|---|---|
| Row-by-row equal? | =A1=B1 or conditional formatting |
| Value in A but not B? | =COUNTIF(B:B, A1)=0 |
| Match keys, return data | XLOOKUP or INDEX/MATCH |
| Values in both columns | Conditional formatting with COUNTIF |
| Case-sensitive equality | =EXACT(A1, B1) |
| Complex multi-column | QUERY |
Sheets Wizard — compare messy columns without exact matches
Sheets formulas need exact matches. Real data has typos, extra spaces, different capitalizations — "John Smith" vs "Smith, John" vs "john smith". Sheets Wizard's AI does fuzzy matching that recognizes near-duplicates. Ask "which customers in file A are missing from file B, ignoring formatting" and it handles the rest.
Install Sheets Wizard →Frequently asked questions
How do I compare two columns in Google Sheets for matches?
Use =A1=B1 in a helper column to return TRUE if the cells match. For 'does A1 exist anywhere in column B?', use =COUNTIF(B:B, A1)>0. To highlight matches visually, use Conditional formatting with a custom formula rule.
How do I find values in one column that aren't in the other?
Use Conditional formatting: select column A, Format → Conditional formatting → Custom formula: =COUNTIF($B:$B, A1)=0 → set a red fill. This highlights values in A that don't appear in B. Alternatively, =FILTER(A2:A100, COUNTIF(B2:B100, A2:A100)=0) returns the missing values as a list.
How do I compare two columns and return a value?
Use VLOOKUP or the more modern XLOOKUP. Example: =XLOOKUP(A1, D:D, E:E, "Not found") looks up A1 in column D and returns the matching value from column E. This is the classic 'match keys and return related data' pattern.
What's the difference between =A1=B1 and EXACT?
=A1=B1 is case-insensitive: 'Apple' equals 'apple'. =EXACT(A1,B1) is case-sensitive: 'Apple' does not equal 'apple'. Use EXACT when case matters (passwords, codes, case-sensitive IDs). Use =A1=B1 for general text comparison where case shouldn't matter.