Excel Formula to Find Duplicates in Two Columns (Step-by-Step)

Finding duplicates across two columns is a critical skill for data cleaning. Excel gives you several approaches depending on whether you want to highlight, list, or remove duplicates.

Method 1: COUNTIF — Mark Duplicates with a TRUE/FALSE Flag

If Column A has your first list and Column B has the second, put this in C2:

=COUNTIF($B:$B, A2)>0

This returns TRUE for every value in A that also exists in B.

Method 2: VLOOKUP — Find Matching Values

=IFERROR(VLOOKUP(A2, $B:$B, 1, 0), "Not Found")

Shows the matched value if found, or "Not Found" if not.

Method 3: Conditional Formatting — Highlight Duplicates Visually

1. Select Column A (e.g., A2:A100)
2. Go to Home → Conditional Formatting → New Rule
3. Choose "Use a formula" and enter: =COUNTIF($B:$B,A2)>0
4. Pick a highlight color and click OK

Method 4: MATCH — Exact Position of Duplicate

=IFERROR(MATCH(A2,$B:$B,0),"No match")

Returns the row number in Column B where the match is found.

Extract Only the Duplicates (Array Formula)

In Excel 365 / Google Sheets, use FILTER:

=FILTER(A2:A100, COUNTIF(B2:B100, A2:A100)>0)

FAQ

How do I find duplicates between two columns in Excel?

Use =COUNTIF($B:$B, A2)>0 in a helper column, or use Conditional Formatting with the same formula.

What is the fastest way to compare two columns in Excel?

Conditional Formatting is the fastest for visual review. COUNTIF is best for filtering programmatically.

Also useful: our Remove Duplicates Tool and the VLOOKUP Generator.