Hello everyone,
I need some help with arrayformula. I am creating an arrayformula in row 1 which will create a “management” label if it detects title of CEO, Manager, etc.
This is how far I’ve reached:
={“Header”;ArrayFormula(IF(SEARCH(“CEO”,T2:T)>0,“Management”,))}
I have not been able to add ISBLANK or make the criteria search for multiple words at a time. Is it possible to search multiple keywords? In multiple columns? If yes, how.
Thanks for your time and help.
I believe an arrayformula combined with array of words will work but let me check when I get up and get back to you.
={“Header”;ArrayFormula(IF(B2:B<>"",(IF(SEARCH(“CEO”,V2:V)>0,“MANAGEMENT”,)),))}
As always, Thanks for responding @ThinhDinh. I figured most of it out- Instead of ISBLANK I used nested IF and combined multiple columns using concatenate. Haven’t figured out multiple keywords (For example, “CEO” OR “MANAGER”) in one search.
Please let me know if you have a suggestion/ you see an issue.
How many Roles do you have in Management Category and Non-Management Category?
Thanks Arun. There is no limit for management category. Non-management is not defined/part of criteria.
Sumit,
Use REGEXMATCH.
An arrayformula works on REGEXTRACT and I no reason to believe that it will not work on REGEXMATCH.
I am pasting the formula as is from one of my projects.
=ARRAYFORMULA(IF(ROW(A:A)=1,"Model",IF(A:A="","",IF(F:F="",IFERROR(REGEXEXTRACT(UPPER(F:F)&" "&UPPER(G:G),UPPER(Control!$G$1))),UPPER(F:F)))))
I think Control!$G$1 contains a string of keywords that you want to match.
Thanks Amit.
I tried REGEXMATCH but there was some issue at the time (I forget 
). May be it was the criteria I was using at the time or it was leading to TRUE/ FALSE value only. Will try again.
Will REGEXMATCH be better than what I am using right now?
={“Roles”;ArrayFormula(if(ArrayFormula(–regexmatch(K2:K, “CEO|Manager”)),“Management”, “”))}
Arun, Its giving me a parsing error.
Delete " and add from your keyboard and also before regex there are 2 hypen (- -)
Can I make detection case insensitive / lower case irrespective of the original text.
Just wrap around both strings with LOWER().
Thanks Thin, Amit, and Arun. You guys are awesome!
Try this as well
B2 Cell
=arrayformula(if(A2:A<>“”,if(regexmatch(A2:A,“CEO|Manager|Director”),“Management”,“”),“”))