Find Duplicates Between Two Columns in Excel

To find duplicates in two columns in Excel, check every value in one column against the other with COUNTIF: =COUNTIF($B$2:$B$9,A2)>0 returns TRUE when the value in A2 also appears in column B. You can show the result in a helper column, highlight it with conditional formatting, or list all matches with FILTER. This guide is for anyone matching customer lists, applicants or IDs from two sources.

The sample data#

The examples use two customer lists: the customer IDs who ordered in March (column A) and in April (column B). The question is which customers ordered in both months.

Three IDs appear in both columns: C-104, C-112 and C-118. Notice that C-112 and C-118 are on different rows in each column. A row-by-row check would miss them, so you need a formula that searches the whole other column.

How to find duplicates in two columns with COUNTIF#

COUNTIF counts how many times a value appears in a range. If a March ID appears in the April column at least once, it is a duplicate between the columns. In C2, type:

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

Fill it down to C9 (Windows: Ctrl+D; Mac: ⌘D). Rows 3, 6 and 8 show TRUE; the rest show FALSE.

• $B$2:$B$9 is the column to search. The $ signs lock it, so it doesn't move as you fill down.
• A2 is the value to look for. It has no $, so it becomes A3, A4 and so on.
• >0 turns the count into TRUE or FALSE.

For a clearer label, wrap it in IF: =IF(COUNTIF($B$2:$B$9,A2)>0,"In both","March only"). To check the other direction, which April customers were also in March, use =COUNTIF($A$2:$A$9,B2)>0 in column D. The COUNTIF function guide covers the syntax.

How to highlight duplicates between two columns#

Conditional formatting colours the matching cells, so you don't need a helper column. Use a formula rule:

1. Select A2:A9.
2. Go to Home > Conditional Formatting > New Rule.
3. Choose Use a formula to determine which cells to format.
4. Enter =COUNTIF($B$2:$B$9,A2)>0.
5. Click Format, pick a fill colour and click OK.

C-104, C-112 and C-118 in column A turn coloured. To colour the matches in column B too, select B2:B9 and add a second rule with =COUNTIF($A$2:$A$9,B2)>0. Write the formula for the first selected cell (A2 or B2); Excel adjusts it for the other rows.

You may see the built-in rule Highlight Cells Rules > Duplicate Values. If you select both columns and use it, Excel colours any value that appears twice anywhere in the selection, including a value repeated inside one column. For "in both columns", the formula rule is more accurate. The guide to highlighting duplicates compares both rules in detail.

List the duplicates with FILTER#

FILTER returns only the rows that meet a condition, so it can list every value found in both columns. It needs Excel 2021 or Microsoft 365. In an empty cell, type:

=FILTER(A2:A9,COUNTIF(B2:B9,A2:A9)>0)

The result spills into three cells: C-104, C-112, C-118. Here COUNTIF checks all eight March IDs at once and FILTER keeps the ones with a count above 0.

Flip the test to =0 to list the March customers who did not order in April: C-101, C-107, C-110, C-115 and C-120. The guide on how to compare two lists shows more ways to spot what is missing from each list.

Count how many values are in both columns#

To get one number instead of a list, add up the matches with SUMPRODUCT:

=SUMPRODUCT(--(COUNTIF(B2:B9,A2:A9)>0))

The result is 3. The -- turns each TRUE into 1 and each FALSE into 0, and SUMPRODUCT adds them. This works in every version of Excel without special keys. If you have a helper column of TRUE and FALSE, =COUNTIF(C2:C9,TRUE) gives the same answer. For counting repeats inside a single column, see how to count duplicates.

Find rows where two columns repeat together#

Sometimes "duplicates in two columns" means something else: rows where the pair of values appears more than once. In an attendance log, the same employee and the same date twice is a double entry, but the same employee on different dates is fine.

COUNTIFS checks both columns at once. In C2, type and fill down:

=COUNTIFS($A$2:$A$6,A2,$B$2:$B$6,B2)>1

Rows 2 and 5 return TRUE: Ana on 3-Mar appears twice. Row 4 is Ana on a different date, so it is FALSE. To delete the extra rows, use Data > Remove Duplicates with both columns ticked; it keeps the first copy. Logs like this are common in an attendance tracker and a job application tracker, where double entries skew the counts.

Why duplicates are not found#

If a value looks the same in both columns but the formula says FALSE, the two cells hold different characters. Check these:

• Extra spaces. "C-104 " with a trailing space doesn't match "C-104". Clean both columns with TRIM, or compare with =COUNTIF($B$2:$B$9,TRIM(A2))>0 if only column A is messy.
• Very long numbers. COUNTIF treats text that looks like a number as a number, and Excel keeps only 15 digits. Two different 16-digit account numbers stored as text can then count as a match. For long IDs, use =SUMPRODUCT(--($B$2:$B$9=A2))>0, which compares the full text.
• Different formats. "C-104" and "C104" are different values. Use SUBSTITUTE to remove the dash from both before comparing.
• Wildcards. COUNTIF treats * and ? in the value as wildcards, so a value like "A*" can match many cells. Rare in IDs, but worth knowing.

COUNTIF ignores capital letters, so "c-104" and "C-104" count as a match. That is usually what you want.

Practise it#

Try the exercise on this page: it checks whether one March customer also ordered in April. Then practise COUNTIF in a live spreadsheet, where you type each formula and it checks it instantly, and use the conditional formatting lesson to highlight duplicate IDs with a formula rule.