Dropdown results matching name in table, result in relevant email address in another cell

[Jackman1]

Hi Folks,

I'm looking at creating a link to an email address where a Dropdown list matches a name in a table and results with the relevant email address being displayed in another cell.

Eg
Dropdown selected in Sheet1 P4 - is "Joe Blogs"
In Sheet 1 P5 I want this to display JoeBlogs@gmail.com

Multiple names and emails (up to 5) will be in the same columns as per the table below (contact 1 = email 1, contact 2 = email 2 etc)

So if dropdown request matches cell in column E display relevant name from column F, etc

Each row is relevant to a different customer, hence the format.

Hope that make sense.

Thanks for any assistance offered.

Shaun

 

[jkpieterse]

This formula creates an email link:

=HYPERLINK("mailto:"&SUBSTITUTE(A2," ","")&"@gmail.com",SUBSTITUTE(A2," ","")&"@gmail.com")

 

[Jackman1]

This formula creates an email link:

=HYPERLINK("mailto:"&SUBSTITUTE(A2," ","")&"@gmail.com",SUBSTITUTE(A2," ","")&"@gmail.com")

Hi Jan,

Thanks for the prompt reply.

Apologies, I should have explained, I don't really require the hyperlinks, just the email address to be displayed which matches the relevant name associated with it. I'm currently trying VLOOKUP and INDEX/MATCH but struggling, not sure if what I'm requesting is possible. I though a table format may help?

Below is what I want to be displayed in Sheet1 P5 (Hyperlink or no hyperlink, not critical) based on contact being selected from the dropdown selected in P4;

 

[jkpieterse]

It depends, your data isn't laid out very handy to retrieve this information, as there are multiple columns. If you reshape your data into two columns: Name and Email address then fetching the email address with a name is indeed simple. If you insist on keeping your current layout, which rules do you have to determine in which column the search must be done and from which column you want to fetch the email address?

 

[Jackman1]

It depends, your data isn't laid out very handy to retrieve this information, as there are multiple columns. If you reshape your data into two columns: Name and Email address then fetching the email address with a name is indeed simple. If you insist on keeping your current layout, which rules do you have to determine in which column the search must be done and from which column you want to fetch the email address?

The problem I have have is that each customer has multiple contacts. In this example 2 contacts. Would it help if the two columns are merged? eg
Instead of Contact 1 and Email 1, 1 column contains both name and email "Joe Blogs JoeBlogs@gmail.com", contact 2 becomes "Rachel Dunne RachelDunne@icloud.com

Sorry if confusing, I'm just trying to calculate a work around.

Another option may be to replicate customers to I only have one column of contacts, and one column of email addresses. This would be my last option.

 

[jkpieterse]

You could use the VSTACK function (requires a Microsoft 365 license) to stack the contact names on top of each other:
=VSTACK(Table1[contact1],Table1[contact2],Table1[contact3])
=VSTACK(Table1[email1],Table1[email2],Table1[email3])
Then you can have your drop-down refer to the first range and use an xlookup against the two combined?