vlookup and email addresses

Hi,
I have a vlookup formula in sheet 1 and in sheet 2 with the list of
names column A and column B with their respective email addresses.

I had the email addresses with hyperlinks in sheet 2.
But when I look up a name on sheet 1, it's not hyperlinked.
I tried hyperlinking it on sheet 1 but when I type in a different name
the email address does not change with the corresponding name.

thanks
youth

Sounds like you just need the appropriate format. Select one of the cells
with the mailto hyperlink from sheet 2, and copy (ctrl+C). Highlight the
column on sheet1 where the email addresses will be returned, then Edit >
Paste Special, and select the 'Formats' radio button and click OK.
--Bruce

"youth" wrote:

> Hi,
> I have a vlookup formula in sheet 1 and in sheet 2 with the list of
> names column A and column B with their respective email addresses.
>
> I had the email addresses with hyperlinks in sheet 2.
> But when I look up a name on sheet 1, it's not hyperlinked.
> I tried hyperlinking it on sheet 1 but when I type in a different name
> the email address does not change with the corresponding name.
>
> thanks
> youth
>
>

You need to use the HYPERLINK function or a macro

=IF(ISNA(MATCH(A1,Sheet2!A2:A30,0)),"",HYPERLINK("mailto:"&VLOOKUP(A1,Sheet2!A2:B30,2,0),VLOOKUP(A1,Sheet2!A2:B30,2,0)))

replace the vlookup formula with your formula and the match range with the
leftmost range in your vlookup formula

--
Regards,

Peo Sjoblom

(No private emails please)


"youth" <ev7101@yahoo.com> wrote in message
news:1129999701.587490.129170@g49g2000cwa.googlegroups.com...
> Hi,
> I have a vlookup formula in sheet 1 and in sheet 2 with the list of
> names column A and column B with their respective email addresses.
>
> I had the email addresses with hyperlinks in sheet 2.
> But when I look up a name on sheet 1, it's not hyperlinked.
> I tried hyperlinking it on sheet 1 but when I type in a different name
> the email address does not change with the corresponding name.
>
> thanks
> youth
>

Peo,
I'm trying to use this formula, but can't seem to make it right.
is A1 the field where the query input is?
If it is, then I'm missing something else.
fyi, I would rather not use macro, if we can get this by using a
formula.

peo,
I tried this formula instead:

=HYPERLINK("mailto:
"&VLOOKUP($B17,replist!$A2:$E25,COLUMNS($A$1:E1),FALSE))

It works but unfortunately I see the words: "mailto"
before the email addresses, how do I not let this show?