Search with multiple values in a field

Hi there!

I am trying to create a formula to do a lookup if one of a certain value is inside a job title.

So for example the values are:
VP, CEO, CIO, CTO, CFO, EVP, Director, Manager etc.

If in the field {Job Title} one of these titles is in the name, I want to return the field "Interesting.

Now I made this:
IF(FIND(“IT”, {Job Title}), “Interesting”, “”) &
IF(FIND(“Innovation”, {Job Title}), “Interesting”, “”) &
IF(FIND(“AI”, {Job Title}), “Interesting”, “”) &
IF(FIND(“digital”, {Job Title}), “Interesting”, “”) &
IF(FIND(“data”, {Job Title}), “Interesting”, “”) &
IF(FIND(“transformation”, {Job Title}), “Interesting”, “”) &
IF(FIND(“information”, {Job Title}), “Interesting”, “”) &
IF(FIND(“CIO”, {Job Title}), “Interesting”, “”)

But this returns:

InterestingInterestingInteresting for a few records.

Any idea what would be the best formula for this?
We can also make it a lookup? Or an array?

Happy to hear your thoughts!

Welcome to the community, @Mark_Norbruis!

You could use an OR function like this:

FIND("IT", {Job Title}), 
FIND("Innovation", {Job Title}),
FIND("AI", {Job Title}),
FIND("digital", {Job Title}),
FIND("data", {Job Title}),
FIND("transformation", {Job Title}),
FIND("information", {Job Title}),
FIND("CIO", {Job Title})

This could also be done using regex, which might be easier to manage:

    {Job Title},

An important note is that whether you use regex or the FIND() function, the match is case-sensitive. Notice how “AI” matches in this example, but “Ai” doesn’t. “Data” and “Information” also didn’t match because of their initial capital letters.

Screen Shot 2021-04-06 at 6.07.19 PM

One way around this is to provide options for both upper- and lower-case versions of certain letters, which is another place where regex has an advantage (I only did a few for this example):

    {Job Title},

Screen Shot 2021-04-06 at 6.09.10 PM

1 Like