Google Sheets - Bypassing the restriction of 50k characters for TEXTJOIN

Question

I'm using QUERY to fetch a list of emails.

Additionally, I need to exclude a couple of non relevant emails. The list of non relevant emails is pretty long (4k +) so instead of manually typing each email, I included an array inside my QUERY.

Here is the formula I came up with:

=QUERY('Raw Data'!A:C,"SELECT * WHERE NOT Col1 MATCHES '"&ARRAYFORMULA(TEXTJOIN("|",TRUE,".*"&'List of emails Exclusion'!A2:A&".*"))&"'")

However TEXTJOIN does not allow more than 50k characters, so I get an error.

I'm guessing I need to nest this inside another QUERY but for some reasons I cannot get it right. Would appreciate any help on this.

Here is my dummy sheet to help understanding my issue.

Answer 1

Here's one approach you may test out:

=filter('Raw Data'!A2:C,isna(xmatch(regexextract('Raw Data'!A2:A,"^(?:.*@)?(.*)"),tocol('List of emails I want to exclude'!A2:A,1))))