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.
