Test if text in a range of cells exists on a single cell

Question

In Sheet 1 I have a list of emails and I would like to add a status (either Good or Bad / true or false) next to it if the email contains any of the substrings in the Sheet 2.

Sheet 2 has a list of substrings that constitute a "bad email" such as @test.com or noreply that are in a list of cells.

Here is the desired output

I've searched around and cannot find similar questions so I think this would be helpful for those searching for the same problem in the future.

Answer 1

Use textjoin() to build a regular expression and use it to test the email addresses, like this:

=arrayformula( 
  ifs( 
    isblank(A2:A), iferror(1/0), 
    regexmatch( A2:A, textjoin("|", true, Bad!B2:B) ), "Bad", 
    true, "Good" 
  ) 
)

To learn the exact regular expression syntax used by Google Sheets, see RE2.