By the way what is your lookup value (A1). Is that a friendly name like cnn
or so . If so try the below
=HYPERLINK(VLOOKUP(A1,M1:N5,2,FALSE),A1)

OR
have a friendly name for each in Col O1:O5 and then
=HYPERLINK(VLOOKUP(A1,M1:O5,2,FALSE),VLOOKUP(A1,M1 :O5,3,FALSE))

If this post helps click Yes
---------------
Jacob Skaria


"bluker" wrote:

Good Day,

hope that you possibly can help me.

I tried below function in an Excel sheet. It works well until I change the
text to display for the hyperlink, for e.g. to cnn instead of
http://www.cnn.com.

Then the link doesnt work anymore. For short links this is no issue, but
unfortunately I have major long links to follow
and would love to make them more user friendly by shortening the displayed
text. Is this possible at all?

I seriously appreciate your help.
Thank you and best regards, Kerry




Start with a table in M1 thru N5:

1 http://www.cnn.com
2 http://www.abc.com
3 http://www.nbc.com
4 http://www.cbs.com
5 http://www.fox.com

In A1 put your data validation dropdown covering the list in column M
In B1 put:

=HYPERLINK(VLOOKUP(A1,M1:N5,2,FALSE))

The VLOOKUP picks the right link and the HYPERLINK function makes the result
"click-able". Enjoy.