How to Compare Two Columns in Excel
"Compare two columns" means different things in different situations. 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 scenarios.
⚡ Quick Answer
For row-by-row equality: =A1=B1 (or use Conditional Formatting). For "does A1 exist anywhere in column B?": =COUNTIF(B:B, A1)>0. For matching and returning related data: =XLOOKUP(A1, D:D, E:E).
Scenario 1: Row-by-row comparison
Are the values in each row equal or different?
Method: Simple equality formula
=A1=B1Returns TRUE if identical, FALSE if different. Copy down column C to check every row. Case-insensitive ("Apple" = "apple").
Method: Labeled comparison
=IF(A1=B1, "Match", "Different")Human-readable output instead of TRUE/FALSE.
Method: Case-sensitive
=EXACT(A1, B1)Returns TRUE only if identical including case. "Apple" ≠ "apple".
Method: Highlight differences with Conditional Formatting
Select columns A and B
Highlight both columns of data.
Home → Conditional Formatting → New Rule
Choose "Use a formula to determine which cells to format".
Enter formula
=$A1<>$B1. Set a fill color for differences.Click OK
Rows where A ≠ B are highlighted.
Scenario 2: Values in A not in B (set difference)
Find items in column A that don't appear anywhere in column B.
Method: COUNTIF for existence check
=IF(COUNTIF(B:B, A1)=0, "Not in B", "In B")COUNTIF returns 0 if A1 doesn't exist anywhere in column B.
Method: Highlight missing values
Select column A
Highlight the column you want to check.
Conditional Formatting → New Rule → Formula
Enter:
=COUNTIF($B:$B, A1)=0Set formatting
Red fill for missing values. OK.
Values in A that aren't anywhere in B are now highlighted red. Repeat with columns swapped to find values in B not in A.
Scenario 3: Match keys, return related data
Column A has customer IDs; column D has customer IDs and column E has customer names. Get the name for each ID in column A.
Method: XLOOKUP (Excel 365 / 2021+)
=XLOOKUP(A1, D:D, E:E, "Not found")Looks up A1 in column D, returns matching value from column E. Fourth argument is what to return if no match.
Method: VLOOKUP (older Excel)
=VLOOKUP(A1, D:E, 2, FALSE)Looks up A1 in the first column of D:E, returns column 2 (E) of the matching row. FALSE means exact match. See our VLOOKUP guide for details.
Method: INDEX/MATCH
=INDEX(E:E, MATCH(A1, D:D, 0))More flexible than VLOOKUP; works when lookup column isn't the leftmost. See INDEX/MATCH guide.
Scenario 4: Find duplicates across two columns
Which values appear in both column A and column B?
Method: COUNTIF
=IF(COUNTIF(B:B, A1)>0, "In both", "Only in A")Marks values that appear in both columns.
Method: Highlight duplicates with Conditional Formatting
Select both columns
Highlight A and B together.
Conditional Formatting → Highlight Cells Rules → Duplicate Values
Excel highlights every duplicate across the combined selection.
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 → Duplicate Values |
| Case-sensitive equality | =EXACT(A1, B1) |
Excel Wizard — compare messy columns without exact matches
Excel formulas need exact matches. Real data has typos, extra spaces, different capitalizations — "John Smith" vs "Smith, John" vs "john smith". Excel Wizard's AI does fuzzy matching that recognizes near-duplicates and lets you compare messy data reliably. Ask "which customers in file A are missing from file B, ignoring formatting" and it handles the rest.
Install Excel Wizard →Frequently asked questions
How do I compare two columns in Excel for matches?
Use =A1=B1 in a helper column to return TRUE if the cells match, FALSE if not. For finding whether a value in column A exists anywhere in column B, use =COUNTIF(B:B, A1)>0. To highlight matches visually, use Conditional Formatting with the formula =A1=B1 as the rule.
How do I find differences between two columns in Excel?
Use Conditional Formatting: select column A, go to Home → Conditional Formatting → New Rule → Use a formula → =COUNTIF($B:$B, A1)=0 → set a color. This highlights values in A that don't appear anywhere in B. Repeat with columns swapped to find values in B not in A.
How do I compare two columns and return a value from another column?
Use VLOOKUP or XLOOKUP. Example: =XLOOKUP(A1, D:D, E:E, "Not found") looks up A1 in column D and returns the corresponding value from column E. This is how you 'compare' by matching keys and returning related data — the core use case for lookup functions.
How do I compare two columns row by row (not entire columns)?
For row-by-row comparison, use =IF(A1=B1, "Match", "Different") in column C. This checks each row's A vs B and returns a label. Use =EXACT(A1, B1) for case-sensitive comparison. To highlight rows where A ≠ B, use Conditional Formatting with the formula =$A1<>$B1.