Using vlookup to copy hyperlinks

May 1, 2017 at 19:55:18
Specs: Excel 2010
I am using the following formula to copy hyperlinks from one worksheet to another and when I click on the link, I am receiving an error that states it is unable to open as the internet site reports that the item you requested could not be found. However, if I go to the worksheet with the link on it, the site pulls up just fine. Please help!

Here is the formula I am using.
=HYPERLINK(VLOOKUP(A83,CAM!A:X,3,FALSE))

message edited by mensayjd


See More: Using vlookup to copy hyperlinks

Report •

#1
May 2, 2017 at 06:25:14
I tried your formula with 3 different links: This thread, an Amazon product page and a Google search result. All three links returned by the formula worked fine. Since I can't replicate your problem, I can't offer any suggestions.

Could you share a couple of the links that are giving you trouble - assuming the target pages don't contain personal or confidential information?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
May 2, 2017 at 06:35:47
I also tried and it didn't work. My solution was to prefix my links with http:// this way the returned text string is converted to a true hyperlink.

Example

A            B          
1  Hotmail     "http://www.hotmail.com" 
2  Yahoo        "http://www.yahoo.com"
 


Note that my addresses had to be flanked with quotes otherwise the site would add other html tags to it and it looked strange. Your addresses DO NOT need the quotes around it.

Now when your vlookup returns the matched address it will be a proper url and will work.

message edited by AlwaysWillingToLearn


Report •

#3
May 2, 2017 at 06:52:43
What is hard is that my links are internal websites for my company so I cannot share them. If I go to the worksheet that they are on and I click on them, they work perfectly. They just do not work when I am using my hyperlink lookup formula to bring them over to the other worksheet.

Report •

Related Solutions

#4
May 2, 2017 at 06:55:50
I am not sure that this really helps me as I am not working with actual website addresses. I am working with hyperlinks that are given a name descriptive name. For example the hyperlink could be: test123 and the website address for that hyperlink is, https://Google.com

Report •

#5
May 2, 2017 at 07:08:52
So from your example above, the friendly name test123 would this be a hyperlink itself?

if so then can you not edit this hyperlink and make sure it is prefixed correctly?
Also you may want to check that the link is copied EXACTLY as it displays in the browsers address bar when you navigate to the site from the working hyperlink.

Unfortunately without seeing the links it is very difficult to help. I appreciate that the links are sensitive and wont work if they are internet sites. Not sure what else to suggest


Report •

#6
May 2, 2017 at 07:10:40
AWTL:

I entered http://www.yahoo.com in my lookup_table with no quotes. mensayjd's formula worked just fine and took me to https://www.yahoo.com/

The only difference is that I was taken to the secure Yahoo site, even though the link did not contain the "s".

IOW, I'm not sure what you mean about the quotes being required.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
May 2, 2017 at 07:40:34
Hey Derby,

Yup, when I tested this it only worked when I prefixed with http:// the s will be added automatically when the link is resolved so we do not need to worry about that.

In terms of the quotes, when I wrote "http://www.hotmail.com" without the quotes and hit preview, it added so many other tags, I tried to delete them and preview again but the same happened.

let me try this below

http://www.hotmail.com

hopefully this will show the tags I am talking about, if not then just ignore me its been a long day :D


Report •

#8
May 2, 2017 at 07:55:17
mensayjd:

OK, I was able to duplicate your problem by using friendly names in Column 3 of the table_array, which is what I believe you are doing.

Here is how I solved the problem:

I added a column to my table_array (Column Y) and stored the actual website address there.

I then used this formula:

=HYPERLINK(VLOOKUP(A83,Cam!A:Y,25,0),VLOOKUP(A83,Cam!A:Y,3,0))

The VLOOKUP pulls the real address, but the HYPERLNK does it's magic by displaying the friendly name and creating a working link.

I tried it with both an internal website as well as an external one and it worked both times.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#9
May 2, 2017 at 08:05:02
I think I understand. In the Preview editing field you saw something like this. (I removed the fore and aft < and > to get it to display as text)

a href="http://www.hotmail.com" target="_blank" rel="nofollow">http://www.hotmail.com</a

If you were seeing all the extra stuff in the pane that showed what was actually going to be posted (not just the Preview editing field ) perhaps you accidentally screwed up the string during an edit. BTDT ;-)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
May 2, 2017 at 08:33:44
Thanks! I ended up writing some simple VBA to convert the worksheet that houses the data with the hyperlinks so it is no longer showing the friendly names and is now showing the actual hyperlinks. Now when my vlookup pulls it over to the worksheet that I am using, it is pulling over the actual web address compared to the friendly name and everything is working. Thanks for your help!

Report •

#11
May 2, 2017 at 08:45:01
I'd be curious to know if my solution will work in your situation. I'm not looking to be "right", I'm just wondering if there is something "different" about your links/friendly names that would prevent my suggestion from working.

It might serve as a learning experience for all.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#12
May 2, 2017 at 14:19:21
I did attempt your solution Derby and it still did not work. I thought for sure that it would as well. There is something weird about our internal addresses.

Report •

#13
May 2, 2017 at 15:18:19
Well, thanks for trying. It's too bad that you can't share your workbook/links. It would (might?) be fun to figure what is going on.

Main thing is that you solved it.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question