Help with arrayformula

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.

1 Like

={“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?

1 Like

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.

2 Likes

Thanks Amit.
I tried REGEXMATCH but there was some issue at the time (I forget :neutral_face:). 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”, “”))}

Screenshot 2020-08-13 at 9.48.43 PM

2 Likes

Arun, Its giving me a parsing error.

Delete " and add from your keyboard and also before regex there are 2 hypen (- -)

1 Like

Can I make detection case insensitive / lower case irrespective of the original text.

Just wrap around both strings with LOWER().

2 Likes

Thanks Thin, Amit, and Arun. You guys are awesome!

1 Like

Try this as well

image

B2 Cell

=arrayformula(if(A2:A<>"",if(regexmatch(A2:A,“CEO|Manager|Director”),“Management”,""),""))

4 Likes