Skip to main content

Search with multiple values in a field

  • March 31, 2021
  • 2 replies
  • 81 views
M
Forum|alt.badge.img

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!

2 replies

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • March 31, 2021

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")
E
Justin_Barrett
Forum|alt.badge.img+21

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.

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

ScottWorld
Terms & ConditionsAccessibility statement