Scalable Regexmatch formular-CodePudding

i need help with the following formula:

IF(REGEXMATCH(LOWER(B3), JOIN("|",Keywords!H$2:H$13)),"unqualified","qualified")

B3 is in this Case the String "I need help". My problem is that id like to use the Formula

IF(REGEXMATCH(LOWER(B3), JOIN("|",Keywords!H$2:H)),"unqualified","qualified")

so i dont always need to match the Row with the Keywords. Otherwise i have Spaces in the join formular and the results are always "unqualified".

Does anyone has an idea how i can rewrite this formula into a more "scalable Version"?

I hope everything i explained was understandable.

CodePudding user response：

Try this out. You can remove the LOWER and make the regex case insensitive

=ARRAYFORMULA(
  IF(ISBLANK(B3:B),,
   IF(
    REGEXMATCH(
     B3:B, 
     "(?i)"&TEXTJOIN("|",TRUE,Keywords!H2:H13)),
    "unqualified",
    "qualified")))

CodePudding user response：

i think my question was a bit too unclear. I solved my problem with this formula. IF(REGEXMATCH(LOWER(B3), JOIN("|",QUERY(G$2:G,"select G Where G is not null"))),"unqualified","qualified")