Vlookup and Hyperlink in Excel

Hey Guys,

I have followed the vlookup and hyperlink function, but i guess im having smoe trouble and i am not really sure where am i going wrong. I have attached the sample sheet. Now what i require cell L4 to diplay is the hyperlink.

this is the formula i have used =IF($J4=",",HYPERLINK(VLOOKUP($J$4,Link,2))).

I get False displayed on that cell and also the hyperlink dont work. I get cannot display the specified file.

I have enlisted the validation list. so When i select say "45 Rick" in J4, i need l4 to display the hyperlink/webaddress to it. Ive searched and searched but couldn't get an answer. The validation list is in AE and the hyperlinks are in AF in the same sheet.

Appreciate the help in advance.

Howzit

i used the following formula in your cell


Differences:

• I included FALSE in the vlookup formula. Omitting false will cause the default option of TRUE, which relies on the list to be sorted in ascending order, otherwise it finds the closest match. FALSE will find the exact match
• I have taken the absolute reference from J4 (in the vlookup) and changed it with an absolute column reference
• changed single quotes to double quotes in the IF part

You will also need to change your named rannge LINK so that the range includes column AE (the lookup value).

I attach my version.

HTH's

Howzit

I just tried to open www.google.com from the formula result and it tries to open the document C:\downloads\www.google.com (where I saved the file).

To actually open google, I think you need to have the "email Address" as "http://www.google.com" in your lists.

Thanks for your help Kiwiman.

However im trying to figure out the last part of this. Im trying the L Cell to display the link in a shorter format. I have changed the link in Cell AF, where when hyperlinking stuff it shows the option of what you want to display it as. Like www.google.com, to display it as Google. The link opens up in AF cell when not the L cell. The L cell displays "cannot open specified object" . So is ther anyway to display it in the text that i want it to ?

thanks in advance.

Howzit

The Hyperlink function has two parts:

1. Link Location - path and file name of the file to be opened or url ...
2. Friendly Name - the text to be displayed in the cell. If omitted the link location is omitted

The current formula omits the "Friendly Name" option, in the formula in L.

I have added a third column to the named range "LINK", to hold the Friendly Name, and modified the formula in L. See the attached file.

This now shows GOOGLE in L, and opens the website google web site using the link address http://www.google.com

I forgot to name the table.. Thanks a lot Kiwiman. It helped a lot