How to Compare Two Columns in Excel to Find Matches
Compare two Excel columns for matches or differences using Conditional Formatting, a simple formula flag, COUNTIF or MATCH, and Go To Special row differences.
The quickest way to compare two columns in Excel is to select both, then run Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values to color every value that appears in both. For an answer you can filter or count, add a flag formula like =A2=B2 or use COUNTIF to find items in one list that are missing from the other. Which method you want depends on one question: are your two columns lined up row by row, or are they just two lists in any order? In Microsoft 365, XLOOKUP has become the cleanest way to find missing items, since its built-in if_not_found argument returns a plain “Missing” label instead of the #N/A error that VLOOKUP throws.
Highlight matches with Conditional Formatting
This is the fastest visual pass and needs no formulas. Select both columns (click the first column header, hold Ctrl, click the second), then go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. In the dialog, leave the first dropdown on Duplicate, pick a fill color, and click OK. Every value that appears in both columns lights up. Switch that dropdown to Unique instead and Excel colors the values that appear in only one column — usually the more useful view when you are hunting for differences.
Two caveats keep people confused here. First, the rule works cell by cell across the whole selection, not row by row, so a match in row 2 of column A will highlight even if its twin sits in row 40 of column B. Second, the highlight does not tell you which column a value came from. It is a great “is this value anywhere in the other list” check, and a poor “are these two rows equal” check.
Flag each row with a formula
When your columns are aligned — row 2 in A should match row 2 in B — a helper column gives you a clean, filterable result. In C2 type =A2=B2 and fill down. Excel returns TRUE where the two cells match and FALSE where they differ. You can then filter column C for FALSE to jump straight to the mismatches.
One thing to know: the = operator is not case-sensitive, so =A2=B2 treats “apple” and “APPLE” as equal and returns TRUE. When case matters — product codes, passwords, anything where “AB12” and “ab12” are different records — use =EXACT(A2, B2) instead. The EXACT function compares two strings character for character and returns TRUE only when capitalization and spacing match exactly, so it also flags stray trailing spaces that a plain = would ignore.
Find items in one list missing from the other
If the columns are two unordered lists — say, invoices you sent versus invoices that got paid — row-by-row comparison is meaningless. You want list membership: which entries in column A never appear anywhere in column B. COUNTIF is the simplest tool for this. In a helper column next to list A, enter:
=IF(COUNTIF($B$2:$B$200, A2)=0, "Missing", "OK")
COUNTIF counts how many times A2 shows up in the range B2:B200. A count of zero means it is missing, so the formula prints “Missing.” Lock the range with dollar signs so it does not shift as you fill down. Filter the helper column for “Missing” to isolate the gaps.
MATCH (or VLOOKUP) does the same job and pairs well with error checks. =ISNA(MATCH(A2, $B$2:$B$200, 0)) returns TRUE when A2 is not found — the 0 forces an exact match, and ISNA catches the #N/A that MATCH throws for a miss. Remember this only checks one direction. To catch items in B that are absent from A, run a second helper column the other way around. Like COUNTIF, both MATCH and VLOOKUP ignore case, so pair them with EXACT if capitalization is meaningful. For a deeper walkthrough of the lookup itself, see how to use VLOOKUP in Excel.
Spot row differences with Go To Special
For a fast, no-formula check of aligned columns, Excel can select the mismatched cells for you. Highlight both columns starting from the left one, press F5 (or Ctrl+G), click Special, choose Row differences, and click OK — or skip the menu with the shortcut Ctrl+\. Excel compares every cell in each row against the cell in the first selected column and selects only the ones that differ. Those cells stay selected, so you can immediately apply a fill color from the Home tab to mark them.
This is strictly a positional, same-row comparison. It compares against the leftmost column in your selection, so it is only meaningful when your two lists are sorted into matching rows. On unordered lists it will flag nearly everything. Use it as the counterpart to COUNTIF: Row differences for aligned data, COUNTIF for membership.
Which method to choose
| Method | Best for | Case-sensitive |
|---|---|---|
| Conditional Formatting (Duplicate/Unique) | A quick visual scan of shared or unique values | No |
=A2=B2 flag column | Aligned rows, filterable TRUE/FALSE result | No |
=EXACT(A2, B2) | Aligned rows where capitalization matters | Yes |
COUNTIF / MATCH / VLOOKUP | Finding items missing between two unordered lists | No |
| Go To Special > Row differences | A fast, no-formula check of aligned rows | No |
Two hidden traps cause most false results. Trailing spaces make "Smith " and "Smith" look different to EXACT and identical-looking to your eye — wrap values in TRIM() if a comparison seems wrong. And a number stored as text (with a small green triangle in the corner) will never match the same number stored numerically, so confirm both columns share one format before you trust the output. Once your comparison is clean, you can safely remove duplicates in Excel, or, if you would rather describe the task in plain language, let one of the newer AI tools inside Excel build the formula for you.
Common questions
How do I compare two columns in Excel using XLOOKUP?
In a helper column enter =XLOOKUP(A2, $B$2:$B$200, "Found", "Missing") and fill it down. The fourth argument, if_not_found, prints “Missing” whenever a value in column A is absent from column B, so you can filter for that word directly with no #N/A errors to clean up.
Why does Conditional Formatting highlight matches that are not on the same row?
The Duplicate Values rule works across the whole selection cell by cell, not row by row, so a value in row 2 of one column lights up even if its match sits in row 40 of the other. For a strict same-row check, use a =A2=B2 flag column or Go To Special > Row differences instead.
Why do two identical-looking values in Excel not match?
Usually one is a number stored as text, marked by a small green triangle in the cell corner, while the other is a real number, or one value has trailing spaces. Confirm both columns share a single format, and wrap values in TRIM() to strip stray spaces before comparing.
How do I compare two columns for case-sensitive matches?
The = operator and COUNTIF both ignore capitalization, so “AB12” and “ab12” count as equal. Use =EXACT(A2, B2) instead, which returns TRUE only when the letters, their case, and spacing all match exactly.