Scalable Regexmatch formular

user_19281712 0
Viewed 48

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.

google-sheets
2 Answers

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")))
user_122540980

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")
user_192817120
Related