Extract common values from two lists

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)