I'm trying to see if a COL A contains anything from a specified list. (if it does, I put say a '1' in COL B, or '0' if not.

I'm currently doing it this slow, insane way. (putting this formula into col B - replace 'I20' with A20 for example).

Is there a better way?

=if(REGEXMATCH(I20,"SOUTHERN"),1,0) + if(REGEXMATCH(I20,"GLASS"),1,0) + if(REGEXMATCH(I20,"HOME"),1,0) + if(REGEXMATCH(I20,"JOHN"),1,0) + if(REGEXMATCH(I20,"ENTERPRISE"),1,0) + if(REGEXMATCH(I20,"BRITISH A"),1,0) + if(REGEXMATCH(I20,"WSELECTRICAL"),1,0) + if(REGEXMATCH(I20,"VIKING"),1,0) + if(REGEXMATCH(I20,"TASCHEN"),1,0) + if(REGEXMATCH(I20,"FRANCY"),1,0) + if(REGEXMATCH(I20,"MODELSHOP"),1,0) + if(REGEXMATCH(I20,"APPLE"),1,0) + if(REGEXMATCH(I20,"CURRYS"),1,0) + if(REGEXMATCH(I20,"WATFORD"),1,0) + if(REGEXMATCH(I20,"WICKES"),1,0) + if(REGEXMATCH(I20,"LEYLAND"),1,0) + if(REGEXMATCH(I20,"IKEA"),1,0)

enter image description here


You can use the 'pipe' symbol | to look for multiple possible matches:


enter image description here

Even better, have a list of words somewhere, JOIN them with pipes, and use that:


enter image description here

| improve this answer | |
  • Beat me to it. My only suggestion is to wrap RegexMatch in an ArrayFormula. Enter Something like this entered in cell B2 would look after the entire Column A: =ArrayFormula(if(len(A2:A),ArrayFormula(--regexmatch(A2:A, "SOUTHERN|GLASS|HOME|JOHN|ENTERPRISE|BRITISH|WSELECTRICAL|VIKING|TASCHEN|FRANCY|MODELSHOP|APPLE|CURRYS|WATFORD|WICKES|LEYLAND|IKEA")),"")) – Tedinoz Jul 9 at 11:00
  • Love the pipe symbol - didn't know we had it, that's awesome. Not the biggest fan of array formulas but was thinking something along those lines might work too @Tedinoz – Christopher Turnbull Jul 9 at 12:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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