How to Compare Two Columns in Excel to Find Matches

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?

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.