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

Formula in D2:

=IF(MIN(IF((COUNTIF(List1, List1)+COUNTIF(List2, List1))*NOT(COUNTIF($D$1:D1, List1))>1, COUNTIF(List1, "<"&List1)+COUNTIF(List2, "<"&List1), MAX(COUNTIF(List2, "<"&List2)+COUNTIF(List1, "<"&List2)+1)))<MIN(IF((COUNTIF(List2, List2)+COUNTIF(List1, List2))*NOT(COUNTIF($D$1:D1, List2))>1, COUNTIF(List2, "<"&List2)+COUNTIF(List1, "<"&List2), MAX(COUNTIF(List1, "<"&List1)+COUNTIF(List2, "<"&List1)+1))), INDEX(List1, MATCH(SMALL(IF((COUNTIF(List1, List1)+COUNTIF(List2, List1))*NOT(COUNTIF($D$1:D1, List1))>1, COUNTIF(List1, "<"&List1), ""), 1), COUNTIF(List1, "<"&List1), 0)), INDEX(List2, MATCH(SMALL(IF((COUNTIF(List2, List2)+COUNTIF(List1, List2))*NOT(COUNTIF($D$1:D1, List2))>1, COUNTIF(List2, "<"&List2), ""), 1), COUNTIF(List2, "<"&List2), 0))) + CTRL + SHIFT + ENTER copied down as far as necessary.

Named ranges
List1 (A2:A13)
List2 (B2:A14)
What is named ranges?

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

Download excel example file.
Filter duplicates from two columns and sort from A to Z.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

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

SMALL(array,k) Returns the k-th smallest row number in this data set.

NOT(logical)
Changes FALSE to TRUE or TRUE to FALSE

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

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

MAX(number1,[number2],)
Returns the largest value in a set of values. Ignores logical values and text.

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. Count unique values in two lists combined in excel
2. Filter unique values from a range using array formula in excel
3. Merge two columns with possible blank cells in excel (formula)
4. Count matching cell values in two columns in excel
5. Filter text values existing in range 1 but not in range 2 using array formula in excel
6. Filter common text values in range 1 and in range 2 using array formula in excel
7. Filter a column and create a new unique list sorted from A to Z using array formula in excel
8. Filter unique distinct text values using “begins with” criterion in a range using array formula in excel
9. Unique distinct list from a column sorted A to Z using array formula in excel
10. Sort values in parallel (array formula)