Vlookup to return Name but hyperlinked

[SamS]

In Sheet 1 I have a list of names (Col A) and their email addresses (Col B), in Sheet 2 I have a lookup that returns the Name frrom Col A but what I was looking for was that when the name was returned it was also a hyperlink that would be linked to the email address. Is this possible to do without VBA, it needs to be able to be used in Excel 2003 at this stage.

Extreme thanks in advance as the rec club is way behind in its raffles (32 weeks) and this would help immensly.

 

[Andrew Poulsom]

Example:

=HYPERLINK("mailto:"&VLOOKUP(A1,Sheet1!A:B,2,FALSE),"TextToDisplay")

Change TextToDisplay to what you want, or omit the argument.

 

[Richard Schollar]

Hi

Here's one way:

Sheet1, your table of names/email addresses:

<b>Excel 2002</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Member</td><td style="font-weight: bold;;">Name</td><td style="font-weight: bold;;">Email Address</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1</td><td style=";">Bob</td><td style="text-decoration: underline;color: #0000FF;;">Bob@somewhere.com</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">2</td><td style=";">Terry</td><td style="text-decoration: underline;color: #0000FF;;">Terry@somewhereelse.com</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">3</td><td style=";">Sandy</td><td style="text-decoration: underline;color: #0000FF;;">Sandy@elsewhere.com</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

Sheet2 with Vlookup:

<b>Excel 2002</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Member</td><td style="font-weight: bold;;">Name & email link</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">3</td><td style="text-decoration: underline;color: #0000FF;;">Sandy</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=HYPERLINK(<font color="Blue">"mailto:"&VLOOKUP(<font color="Red">A2,Sheet1!$A$2:$C$4,3,0</font>),VLOOKUP(<font color="Red">A2,Sheet1!$A$2:$C$4,2,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[SamS]

Thanks guys, you are legends.

 

[SamS]

This is an afterthought and not important but could you also include a "Subject" in this formula.

 

[Richard Schollar]

How so? Can you explain what you mean by the inclusion of "subject" - perhaps provide an example of what the result would look like.

 

[SamS]

Richard, I was looking to include a small piece of text so that when the email opens there will be some text e.g. you have won 2nd prize in the SUBJECT field of the email header.

 

[Andrew Poulsom]

Example:

=HYPERLINK("mailto:"&VLOOKUP(A1,Sheet1!A:B,2,FALSE)&"?subject=Hello","TextToDisplay")

 

[SamS]

That is awesome, thanks so much.