Author: Oscar Cronquist Article last updated on December 04, 2017

Array Formula in B11:

=INDEX($B$3:$B$7, SMALL(IF(COUNTIF($D$3:$D$8, $B$3:$B$7)=0, MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), ""), ROWS($A$1:A1)))

To enter an array formula press and hold CTRL + SHIFT simultaneously, then press Enter once. Release all keys.

The formula bar now shows the formula enclosed with curly brackets telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

Copy cell D2 and paste it down as far as needed.

Array Formula in B15:

=INDEX($D$3:$D$8, SMALL(IF(COUNTIF($B$3:$B$7, $D$3:$D$8)=0, MATCH(ROW($D$3:$D$8), ROW($D$3:$D$8)),""), ROWS($A$1:A1)))

Copy cell D9 and paste it down as far as needed.

Sorted from A to Z

Array Formula in B11:

=INDEX($B$3:$B$7, MATCH(SMALL(IF((COUNTIF($F$2:F2, $B$3:$B$7)+COUNTIF($D$3:$D$8, $B$3:$B$7))=0, COUNTIF($B$3:$B$7, "<"&$B$3:$B$7)), 1), COUNTIF($B$3:$B$7, "<"&$B$3:$B$7), 0))

Array Formula in B15:

=INDEX($D$3:$D$8, MATCH(SMALL(IF((COUNTIF($F$9:F9, $D$3:$D$8)+COUNTIF($B$3:$B$7, $D$3:$D$8))=0, COUNTIF($D$3:$D$8, "<"&$D$3:$D$8)), 1), COUNTIF($D$3:$D$8, "<"&$D$3:$D$8), 0))

Blog posts you may find interesting:

How to find common values from two lists
How to remove common values between two columns
Filter common values between two ranges using array formula in excel
Filter values existing in range 1 but not in range 2 using array formula in excel
Highlight common values in two lists using conditional formatting in excel

Download excel *.xlsx file

Compare and filter values in two lists.xlsx

Compare and filter values in two lists.xls
(Excel 97-2003 Workbook *.xls)