fbpx

How to find duplicate values in two columns in Excel using formula

Share

Facebook
Twitter
LinkedIn

Let’s say you have two lists of values in Excel and want to find out all the common values (ie duplicates) and extract them. In this article, let me explain the formulas for this.

Formula for counting number of duplicate values in two columns:

Here is the formula to count number of common values between two columns.

=COUNT(XMATCH(D4:D20,B4:B16))

'Generic formula pattern

=COUNT(XMATCH(list2,list1))

How does this formula work?

The XMATCH(list2, list1) finds the position of every item in list2 in list1. Then it returns an array of these positions (or #N/As if the value is not in the other list).

COUNT(XMATCH(..)) simply counts the numbers in these positions. So the errors are ignored and we get the count of common values.

Limitations of this formula

The above formula requires XMATCH function, which is available only in Excel 365 and Excel online. If you are using an older version of Excel, refer to this page for alternative approaches.

Extracting the duplicate values between two columns with Excel formula

We can use the same approach to find all the duplicate values and extract them as a list. Here is the formula for that.

=LET(arr, XLOOKUP(B4:B16,D4:D20,D4:D20,""),FILTER(arr, arr<>""))

'generic formula pattern

=LET(list1, <range goes here>, list2, <range goes here>, 
arr, XLOOKUP(list1,list2,list2,""),FILTER(arr, arr<>""))

Formula Explanation

Let’s go inside out.

  • XLOOKUP(B4:B16,D4:D20,D4:D20,””): This lookups every item in the first list against second list and returns the value from second list if found and blank space other wise. [related: learn more about XLOOKUP]
  • LET(arr, XLOOKUP(..): We store the xlookup output (which would be an array the size of first list) in to a variable called arr.
  • FILTER(arr, arr <> “”): We take the output of the xlookup, which is stored in the variable arr and remove any blank values (ie the value is not in the second list).

Here is the sample output of both of these formulas (counting duplicate values and extraction of duplicate values).

Download Example Workbook

Click here to download the sample workbook with these formulas. Play with the data & formulas to learn how they work.

Other ways to extract duplicate values from two columns:

You can also use Excel features like pivot tables, power query or conditional formatting to deal with this issue. Refer to below pages for the explanation of these powerful techniques.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Leave a Reply