Extract common values from two lists

Generic formula 

=FILTER(list1,COUNTIF(list2,list1))

Explanation 

To compare two lists and extract common values, you can use a formula based on the FILTER and COUNTIF functions. In the example shown, the formula in F5 is:

=FILTER(list1,COUNTIF(list2,list1))

where list1 (B5:B15) and list2 (D5:D13) are named ranges. The result, values that appear in both lists, spills into the range F5:F11.

How this formula works

The FILTER function accepts an array of values and an "include" argument which filters the array based on a logical expression or value.

In this case, the array is provided as the named range "list1", which contains all values in B5:B15. The include argument is delivered by the COUNTIF function, which is nested inside FILTER:

=FILTER(list1,COUNTIF(list2,list1))

COUNTIF is set up with list2 as range, and list1 as criteria. Because we give COUNTIF eleven criteria values, COUNTIF returns eleven results in an array like this:

{1;1;0;1;0;1;0;1;0;1;1}

Notice the 1's correspond to items in list2 that appear in list1.

This array is delivered directly to the FILTER function as the "include" argument:

=FILTER(list1,{1;1;0;1;0;1;0;1;0;1;1})

The FILTER function filters list1 using the values provided by COUNTIF. Values associated with zero are removed; other values are preserved.

The final result is an array of values that exist in both lists, which spills into the range F5:F11.

Extended logic

In the above formula, we use the raw results from COUNTIF as the filter. This works because Excel evaluates any non-zero value as TRUE, and zero as FALSE. If COUNTIF returns a count greater than 1, the filter will still work properly.

To force TRUE and FALSE results explicitly, you can use ">0" like this:

=FILTER(list1,COUNTIF(list2,list1)>0)

Remove duplicates or sort

To remove duplicates, just nest the formula inside the UNIQUE function:

=UNIQUE(FILTER(list1,COUNTIF(list2,list1)))

To sort results, nest in the SORT function:

=SORT(UNIQUE(FILTER(list1,COUNTIF(list2,list1))))

List values missing from list2

To output values in list1 missing from list2, you can reverse the logic like this:

=FILTER(list1,COUNTIF(list2,list1)=0)