Author: Oscar Cronquist Article last updated on August 12, 2018

Question: I have two ranges or lists where I want to extract duplicates?

Answer:

extract-a-list-of-duplicates-from-two-columns-combined

Excel 2007 formula in D2:

=IFERROR(IFERROR(INDEX(List1, MATCH(0, COUNTIF(D1:$D$1, List1)+IF(COUNTIF(List1, List1)>1, 0, 1), 0)), INDEX(List2, MATCH(0, COUNTIF(D1:$D$1, List2)+IF((COUNTIF(List2, List2)+COUNTIF(List1, List2))>1, 0, 1), 0))), "") + CTRL + SHIFT + ENTER

copied down to D20.

Earlier Excel versions:

=IF(ISERROR(INDEX(List1, MATCH(0, COUNTIF(D1:$D$1, List1)+IF(COUNTIF(List1, List1)>1, 0, 1), 0))), INDEX(List2, MATCH(0, COUNTIF(D1:$D$1, List2)+IF((COUNTIF(List2, List2)+COUNTIF(List1, List2))>1, 0, 1), 0)), INDEX(List1, MATCH(0, COUNTIF(D1:$D$1, List1)+IF(COUNTIF(List1, List1)>1, 0, 1), 0))) + CTRL + SHIFT + ENTER

copied down to D20.

Named ranges
List1 (A2:A20)
List2 (B2:B7)
What is named ranges?

How to implement array formula to your workbook
Change named ranges. If your duplicates list starts at, for example, F3. Change D1:$D$1 in the above formulas to F2:$F$2.

Download excel sample file for this tutorial.
how-to-extract-a-list-of-duplicates-from-two-columns-in-excel.xlsx
(Excel 2007 Workbook *.xlsx)