Check a column of strings on another column of substrings-CodePudding

I want to check a column of strings on another column of substrings to see the strings contain any of the substrings.

I am currently attempting =SEARCH(lower('Sheet2'!$A$1:$A$100),lower(B1)) and would like a True/False response.

Thanks in advance.

An example of Sheet2 would be:

    A
1   Hello
2   Hi
3   I said she

an example of Sheet1 with the expected result in column C would be:

    A            B                C
1   23  There are many          FALSE
2   45  I said he is slow       FALSE
3   3   I said she is bad       TRUE
4   78  he yelled hello         TRUE

Any help is appreciated

EDIT: link to example - https://docs.google.com/spreadsheets/d/1c2pskSYsGs12Yjbn-5gORQ22mDSaC9cSnp1nWeULlf4/edit?usp=sharing

CodePudding user response：

In Sheet1!C1:

=ArrayFormula(IF(B:B="",,REGEXMATCH(LOWER(B:B),JOIN("|","\b"&FILTER(LOWER(Sheet2!A:A),Sheet2!A:A<>"")&"\b"))))

You haven't shared a link to a spreadsheet, so this is untested on any actual data. Your locale is also unknown, which may required modifications as well. So if this formula doesn't work as provided, share a link to your sample spreadsheet.