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:

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


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.


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.


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 :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


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().


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

1 Like

Try this as well


B2 Cell