My QUERY+ARRAYFORMULA TEXTJOIN sheet doesn't show result as expected

Question

I want to extract data from Column C (which contains about 1200 rows of numbers) that isn't appear inside Column B into Column E. So far i tried QUERY MATCHES and combined it with TEXTJOIN but it returned nothing. I also tried .* symbol but also doesn't work.

I'm not sure if it's because the TEXTJOIN returned result is too long. But i tried the formula in another cell and it seems to worked fine, so i have no idea why it doesn't work. Here's dummy sheet to take a look. Thank you for your assistance!

Answer 1

try:

=IF(E2 = "ID49", QUERY(A:C, 
 "SELECT C 
  WHERE NOT C MATCHES "&ArrayFormula("'.*"&textjoin(".*|.*",1,B2:B)&".*'")&" 
    AND A >  date '"&TEXT(DATEVALUE(E4),"yyyy-mm-dd")&"' 
    AND A <= date '"&TEXT(DATEVALUE(E6),"yyyy-mm-dd")&"'",1))