Question: I have three ranges or lists and I need to extract duplicates from those ranges combined using excel array formula?

Answer:

### Excel 2007 array formula in D2:

=IFERROR(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))), INDEX(List3, MATCH(0, COUNTIF(D\$1:\$D1, List3)+IF((COUNTIF(List3, List3)+COUNTIF(List1, List3)+COUNTIF(List2, List3))>1, 0, 1), 0))), "") + CTRL + SHIFT + ENTER

copied down to D20.

Named ranges
List1 (A2:A20)
List2 (B2:B7)
List3 (C2:C8)
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-three-columns-in-excel.xlsx
(Excel 2007 Workbook *.xlsx)

Functions in this article:

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

IFERROR(value;value_if_error) Returns value_if_error if expression is an error and the value of the expression itself otherwise

This blog article is one out of six articles on the same subject.

### Related posts:

Extract a list of duplicates from two columns combined using array formula in excel

Filter duplicates from two columns combined and sort from A to Z using array formula in excel

Extract a list of duplicates from a column using array formula

Extract a unique distinct list from two columns using excel 2007 array formula

Merge three columns into one list in excel