Google Sheets Query for a Part of a String-CodePudding

I have a sheet that looks something like this:

ColA                    | ColB 
about                   | about us
about privacy           | privacy policy
brands                  | brand names
blog category legal     | legal posts
blog category treatment | treatment posts
...                     | ...

My hope is to get a formula that will return TRUE or FALSE if any part of the string in ColB exists in the entire A2:A range. For example, the cell B3 that contains "privacy policy" would return TRUE since "privacy" in the A2:A range.

I've tried a couple different formulas:

=IF(ISBLANK($A$2:$A),"",QUERY($A$2:$A,"where lower(A) contains '"&JOIN("'and lower(A) contains'",SPLIT(LOWER($B2:$B)," "))&"'",TRUE))

AND

=IF(QUERY(B2:B,"select A where A contains '"&B2&"'"),TRUE,FALSE)

Neither of these are giving me what I'm hoping for. Any help is appreciated!

CodePudding user response：

unusual, but try:

=ARRAYFORMULA(REGEXMATCH(B1:B5, "(?i)"&SUBSTITUTE(TRIM(QUERY(A1:A,,9^9)), " ", "|")))

CodePudding user response：

I was able to find what I was looking for here .