Email / Name reference formula help

Vaibhav Khasgiwala

Hi,

I am trying to create a formula where an entered email address automatically populates the person's name associated with that email in an adjacent column (see below). The data is referenced from a master sheet of people's email/name contact information. I have this function in a different sheet and it works perfectly using the formula below.

Sheet 1:

=INDEX({Physician Name}, MATCH([Submitted By Email]@row, {Email from TRA Contact Sheet}, 0))

where "Submitted by email" is matched to the name -- "{Physician Name}" -- of that person's email in the reference sheet "TRA Contact Sheet."

Sheet 2:

If I cut and paste this formula exactly as is into a new sheet I am creating, and adjust the Match Column name for the new sheet, but keep the same references, it is not working and giving the wrong name:

=INDEX({Physician Name}, MATCH([Employee Email]@row, {Email from TRA Physician Contact}, 1))

Of note, if I put a "0" at the end for unsorted like in the original formula, it gives me a "NO MATCH" error. It only works (incorrectly) if I put "1"

One thing to note is that in the Sheet 1, the email is being autopopulated by a form. In Sheet 2, I am manually typing the email address and it is populating from contact list of saved users in SS.

Any ideas??

Thanks in advance.

Email Name Not Working.jpg

Working Email Name formula.jpg

Paul Newcome

When the form auto-populates... Are the users entering their email? How exactly is the form populating the email?

Vaibhav Khasgiwala

Hi Paul,

Yes, users are entering their emails into the form which is populating in "Sheet 1." Then the formula in the sheet is matching the email to their name on the reference sheet.

Interestingly, I just tried to manually enter my own email in Sheet 1 (not via the form) and the formula doesn't work! I think because when you enter manually, it populates what is in the smart sheet contact list? But when entered via the form, it keeps the email format as name@domain.com. But when you the email address manually, it automatically populates as " (SS initials logo) First Last Name." I think maybe because the reference sheet has the email as the actual name@domain, it's not seeing the correct email to find the associated name.

I guess if this is the case, then I guess I can leave the formula out in Sheet 2 and just have the email field which automatically shows the name rather than the actual email. My only question would be that if I do this, would automations still work going to the person's email even though it's showing as the SS contact name in the sheet (but when you click on it, it does show the contact email as well).

Thanks!

Paul Newcome

You would need to compare either Name to Name or Email to Email. If you compare Email to Name then the text strings don't match and it won't work as you are seeing.

As long as the data in the contact column is an actual contact then SS will recognize it as such and send the automation accordingly.

Vaibhav Khasgiwala

Thank you very much!

Paul Newcome

Happy to help. 👍️