How to use 'not in' in google sheet query?

Question

Hi all,

I want to use a google query in cell F3 to filter the columns C&D. It depends on what is the Product in cell A3:A7. In the screenshot above, the rows that I want to be excluded is Product A and C, so the query able to help me remove the rows with Product A and Product C. I tried to use not in in the query, but it is not flexible as right now in cell A3:A7 only have 2 products want to be excluded, in future there might be more or less.

The expected output shown in the screenshot above (column F,G). If there is any other formula can achieve this, please share with me as well.

Any help will be greatly appreciated!

Answer 1

In google-sheet QUERY() function you can use not matches as equivalent of Not In SQL condition. Try-

=QUERY(C3:D,"where not D matches '" & TEXTJOIN("|",1,A2:A) & "'")