Use FIND() to look for two strings in an array

[Cody_Winchester]

I have table where I’m tracking attendance versus enrollment of a training workshop. I’m using a name field that also has a lookup field displaying whether someone goes by a formal name or an alias. I have another field that has an array of all attendees of the workshop an individual has enrolled in. I’ve had some success finding the enrollee’s name in the array of attendance to determine whether the enrollee is among those in the array. However, I’m running into an issue where, in some cases the enrollee, though listed as using an alias, may in fact have attended the workshop under their formal name. So, I need to use a formula that can search for and find either the alias or the formal name in the array, but it doesn’t seem to be working.

Here’s what I have in the case of an enrollee who uses an alias who also attends the workshop using that alias:

FIND(
(IF({Alias (from Name)},{Alias (from Name)},Name)),
{Workshop Session 000 Attendance})

I have tried including AND, but nothing is found:

IF({Alias (from Name)},
FIND(
AND({Alias (from Name)},Name),{Workshop Session 000 Attendance}),
FIND(Name,{Workshop Session 000 Attendance}))

I have also tried including OR, and nothing is found:

IF({Alias (from Name)},
FIND(
OR({Alias (from Name)},Name),{Workshop Session 000 Attendance}),
FIND(Name,{Workshop Session 000 Attendance}))

I can’t see what I’m missing. Any help would be appreciated.

[Kamille_Parks]

Your first formula is the closest. I suspect your issue might be the Lookup field appearing as an array vs a string. try:

FIND(
  IF({Alias (from Name)},{Alias (from Name)}&"",Name),
  {Workshop Session 000 Attendance}
)

[Cody_Winchester]

Thanks for your reply @Kamille_Parks. Unfortunately, this has no effect. I need to FIND either one or the other strings (Alias, or Name) within the Workshop Session Attendance field (which is an array of multiple linked records). Do you have any other suggestions for how to perform a search in the way I described?

[Kamille_Parks · Accepted solution]

What do you mean by “no effect”?

Find only works on strings, so any array fields (Linked Records or Lookups) need to be represented as strings. Adding &"" rectifies that, or you could use a Rollup field that uses an aggregation formula that converts to string like ARRAYJOIN().

If you are trying to find {Name} in the string even if there is an {Alias}, then you need either two FINDs or one REGEX function.

FIND({Name}, {Workshop Session 000 Attendance}) + 
FIND({Alias (from Name)}, {Workshop Session 000 Attendance})

[Cody_Winchester]

Hi @Kamille_Parks , the REGEX worked! Thanks for that! What I had meant before of having “no effect” was that the CONCATENATE operator, “&,” didn’t produce any different results. I appreciate your time!