The FILTER function using something like “IN” operator – selection of items presented in some list

The FILTER function is simple and at the same time very powerful – it can limit the table to only selected lines (and then summarize them if needed…).

But how to use FILTER to show only values presented in some list? I mean something like the “IN” operator in other languages.

E.g. here I want to display, from the yellow table, only the items that are in the blue table.

And I don´t like to use the complicated:

• =FILTER(A:B;(B:B=”red”)+(B:B=”blue”))

Instead, I can use the COUNTIFS function, like:

• =FILTER(
     A:B;
     COUNTIFS(D:D;B:B)>0)

This takes only rows, where when you try to count values from B in D, the result is higher then zero.

And yes, you can use this shorter, but possibly less clear way:

• =FILTER(
     A:B;
     COUNTIFS(D:D;B:B))