Display an email address based on a name selected in a separate contact field

Good Morning,

I have a sheet called "RFIs" used to track requests for information. I also have a contact list populated with my requestor names and email addresses.

The RFI sheet has a field called requestor it is a single select drop down field pulled from the Contact list. The Requestor field displays the name of a person originating the request.

I would like to display the requestors eMail address in a separate field called eMail. I would like that to automatically fill after the Requestor has been selected. Making it a hyper link would be absolutely awesome.

Thank You

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    Your helper sheet has 3 columns - Name, Email, and Phone - populated with that info for each person...

    Then on your main sheet, you have the same three columns. On this sheet, the Email column can be a Contact List type column, which would enable you to run automations like alerts to the emails in that column.

    For Email:

    =INDEX({Reference to Helper sheet Email column}, MATCH(Name@row, {Reference to helper sheet name column}, 0))

    For Phone:

    =INDEX({Reference to Helper sheet Phone column}, MATCH(Name@row, {Reference to helper sheet name column}, 0))

    As long as your names match between the main and helper sheets, it will pull the correct email and phone each time you select the name on your main sheet.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    You could do this with a helper sheet. Create two columns in your helper sheet, one for Name, the other for Email, and populate with same from your contact list.

    In your RFIs sheet, in the eMail field, use INDEX/MATCH formula:

    =INDEX({Helper Sheet Email}, MATCH(Name@row, {Helper Sheet Name}, 0)

    When creating the index match formula, follow the on screen prompts to create the references to your helper sheets.

    This formula will get the Email value from the helper sheet row where the Name is the same as the Name on the RFIs sheet.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Dan Pollino
    Dan Pollino ✭✭✭

    Good afternoon Jeff,

    I've been trying to get this formula to work as well with little success. I have three columns on a helper sheet, first name, last name and email address. I'd like the email address to automatically populate a cell when the last name on the sheet matches the last name on the helper sheet. Any help would be greatly appreciated. This is my first post so, if I can share the sheets I'd be happy to. Thanks!

  • Tammy Luther
    Tammy Luther ✭✭✭✭

    I am looking to do the same.

    Select a name in a name column and then have it auto populate their email and phone number in the next two columns.

    I understand the cheater sheet and have no issues creating, I'm trying to figure out the formula.

    If there is any assistance I would greatly appreciate the help. Thanks

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    Your helper sheet has 3 columns - Name, Email, and Phone - populated with that info for each person...

    Then on your main sheet, you have the same three columns. On this sheet, the Email column can be a Contact List type column, which would enable you to run automations like alerts to the emails in that column.

    For Email:

    =INDEX({Reference to Helper sheet Email column}, MATCH(Name@row, {Reference to helper sheet name column}, 0))

    For Phone:

    =INDEX({Reference to Helper sheet Phone column}, MATCH(Name@row, {Reference to helper sheet name column}, 0))

    As long as your names match between the main and helper sheets, it will pull the correct email and phone each time you select the name on your main sheet.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Tammy Luther
    Tammy Luther ✭✭✭✭

    @Jeff Reisman

    Jeff - THANK YOU VERY MUCH - this has been a life saver and I truly appreciate your formulas !!

    This is the best community to help everyone out!

    Thank you again!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!