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

Extract a list of duplicates from a column using array formula in excel
Extract a list of duplicates from two columns combined using array formula in excel
Extract a list of duplicates from three columns combined using array formula in excel
Extract a list of alphabetically sorted duplicates from a column in excel
Filter duplicates from two columns combined and sort from A to Z using array formula in excel
Extract duplicates from a range using excel 2007 array formula

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

Answer:

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)

I have written a previous post about extracting duplicates from one column (range): How to extract a list of duplicates from a column in excel

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.

Extract a list of duplicates from a column using array formula in excel
Extract a list of duplicates from two columns combined using array formula in excel
Extract a list of duplicates from three columns combined using array formula in excel
Extract a list of alphabetically sorted duplicates from a column in excel
Filter duplicates from two columns combined and sort from A to Z using array formula in excel
Extract duplicates from a range using excel 2007 array formula

Related posts:

1. Extract a list of duplicates from three columns combined using array formula in excel
2. Filter duplicates from two columns combined and sort from A to Z using array formula in excel
3. Extract a list of duplicates from a column using array formula in excel
4. Extract a unique distinct list from two columns using excel 2007 array formula
5. Extract largest values from two columns using array formula in excel
6. Extract duplicates from a range using excel array formula
7. Extract a unique distinct list from three columns in excel
8. Extract a list of alphabetically sorted duplicates from a column in excel
9. Comparing two columns and sum unique values using array formula in excel
10. Merge two columns into one list in excel