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

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.

  • Extract common values between two tables with Power Query
  • Extracting duplicate values using formulas in older Excel
  • Compare two columns and highlight duplicate values
  • Using Power Pivot to combine two columns (joins)
  • Counting unique values with a formula (older Excel)

The post How to find duplicate values in two columns in Excel using formula appeared first on Chandoo.org – Learn Excel, Power BI & Charting Online.

Original source: https://chandoo.org/wp/how-to-find-duplicate-values-in-two-columns-in-excel-using-formula/

Tags: , ,
Close Menu