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

James Glenn - NOAA Federal

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

Jeff Reisman

https://community.smartsheet.com/discussion/comment/337920#Comment_337920

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.

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.

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

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

https://community.smartsheet.com/discussion/comment/337920#Comment_337920

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.

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!