Excel: How to find and count duplicate values in two lists

Use COUNTIF in Excel to quickly count how many values in one list appear in another.

I recently had a Master List of email addresses, and another list of Reject email addresses that needed to be excluded from this (because we no longer wanted to email these people).

Before I removed them from the mailing software, I wanted to see how many duplicates were in the list. (I didn’t want to accidentally wipe out the list by being over-zealous with the rejects!)

To do this, I used the COUNTIF function in Excel. (I’m using Excel 2016 for Mac, but this should work in any modern version of Excel.) This is much safer than a VLOOKUP for simple work. (See this article for the dangers of VLOOKUP.)

The screenshot below shows my spreadsheet. I replaced the email addresses with simple letters here, to make it clearer to see what’s going on.

Find duplicate values in Excel
Find duplicate values in Excel

I created three columns, for my Master List, my Reject List, and whether this row in the Reject List contains a duplicate.

The basic formula, as you can see in the screenshot below, is then:

COUNTIF(Master-List-Range, Row-From-Reject_List)

At the bottom I just did an Auto-sum. In this example, 3 rows are duplicates.