Explanation
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)
