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:

IF(
OR(
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})
),
"Interesting")

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

IF(
  REGEX_MATCH(
    {Job Title},
    "IT|Innovation|AI|digital|data|transformation|information|CIO"
  ),
  "Interesting"
)

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

IF(
  REGEX_MATCH(
    {Job Title},
    "IT|Innovation|AI|[dD]igital|[dD]ata|[tT]ransformation|[iI]nformation|CIO"
  ),
  "Interesting"
)

Screen Shot 2021-04-06 at 6.09.10 PM

1 Like