Email / Name reference formula help

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.


Tags:

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • 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
    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!