» Retrieving Values that are Common to Two Lists

Problem:

We want to create a new list consisting of the values that are common to both List1 (column A) and List2 (Column B).

Solution:

Use the INDEX, SMALL, IF, COUNTIF, and ROW functions as shown in the following Array formula:
{=INDEX($A$2:$A$9,SMALL(IF(COUNTIF($B$2:$B$9,$A$2:$A$9)>0,ROW($A$2:$A$9),1000),ROW()-ROW($D$2)+1)-ROW($D$2)+1)}
Enter the formula in cell D2 and copy it down the column until the #REF! error is returned.