1

In Google Sheets I have an array named "Tech_issues" with the following values:

Array name value 1 value 2 value 3 value 4
Tech_issues crash froze glitch error

I'm looking to create a formula in column B that I can use to return value X if the corresponding cell in column A contains one (or more) of the words in the array, for example...

Survey response Tech issue
system crashes way too often X
I encountered an error X
Nice design
Sell more products

There are other arrays and queries I want to use for other columns as well, this is just one example so if I can get this working I can do the rest.

So far I've tried the following but to no avail...

=IF(REGEXMATCH(A2,Tech_issues), "X", "")

=isnumber(search(Tech_issues,A2))

=IF(ISNUMBER(SEARCH(Tech_issues, A2)),"X","")

=ArrayFormula(REGEXMATCH(A2, JOIN("|",Tech_issues)))

HELPPPPP

1 Answer 1

0

This is an array formula that will return all the values or a blank for an entire column.

=INDEX(
   IF(
     REGEXMATCH(
       A2:A, "(?i)"& JOIN("|", Tech_issues)), 
     "X",))

To return only a single row use,

=INDEX(
   IF(
     REGEXMATCH(
       A2, "(?i)"& JOIN("|", Tech_issues)), 
     "X",))
  • Tech_issues should be a named range or a reference to a range containing your keywords eg. B2:B10
  • (?i) is prepended to make the regular expression case insensitive.
2
  • 1
    thanks! This almost got it, but with a bit of tweaking I've got it working perfectly - =INDEX(if(REGEXMATCH(upper(A2),TEXTJOIN("|",1,upper(B2:B10))),"X","")) Apr 5 at 14:22
  • @flipskyrain Sorry typo in original formula. Did it on my phone & made a mistake transferring it. Per INDEX vs. ARRAYFORMULA they are interchangeable here, but I switched to INDEX as suggested since it's shorter. Adding UPPER() twice is longer but not better than case-insensitive regex (?i) which does the same thing. A2 instead of A2:A means a single formula no longer returns multiple results. You'd need an additional formula for each row (changes mean editing all of them). Empty text "" can introduce issues & unnecessary: ISBLANK(IF(0,1,))=true but ISBLANK(IF(0,1,""))=false
    – Blindspots
    Apr 5 at 16:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.