Sorting of Contact list with Allow Multiple Contacts in a cell

GregoryAA

I have a sheet that allows multiple contacts in a cell. I was trying to use a formula to pull the persons actual name from another sheet using INDEX from the contact list in that cell. My other sheet contains all of the email addresses in one column, their actual name in another and department in another. This makes it very difficult to index, and I'm not sure there is any way to index from multiple entries (if so that would solve my issue). But the first person in the contact should be the owner of the process, and I can pull that email to index. My issue is, once a person selects their email, and then perhaps adds more people, the cell sorts alphabetically, which then makes my index select the wrong person and department for responsible (if their name is sorted to 2nd, 3rd ... ). Is there anyway to tell the cell not to sort the email address?

Example formula: =IFERROR(IF(FIND(",", JOIN(Email@row)) > 1, INDEX({Service Team Roster Range 4}, MATCH((LEFT((JOIN(Email@row)), FIND(",", (JOIN(Email@row))) - 1)), {Service Team Roster Range 1}, 0)), INDEX({Service Team Roster Range 4}, MATCH(Email@row, {Service Team Roster Range 1}, 0))), "N/A")

This Joins the contact list cell and looks to see if there is more than 1 email listed. If so, it pulls the first email and looks up the persons name (and department depending on the index). If only one contact is listed, it simply pulls the data using the one email address.

As I said, since it is pulling the first email listed in the contact cell, it may pull the wrong persons information into this cell because once you enter the contacts and continue, it sorts the contact list.

Example: BB is the owner and AA is the stakeholder. When you select BB them AA it looks right until you continue. SmartSheet sorts the cell and now it is listed as AA@xxx.com, BB@xxx.com.

Paul Newcome

You are going to need to use a separate column for the email you want to IDNEX against and then another column for the rest of the emails.

GregoryAA

I wanted to avoid that, but certainly is a easy way to avoid the hassle. Thank you.