Vlookup a Hyperlink on another Sheet that is to a redirected Website?

gmooney

Board Regular
Joined
Oct 21, 2004
Messages
168
I would like to have cell A1 in DATA do a vlookup over to ECRMDATA and find and recognize a hyperlinked cell that goes to a redirected website....

Every time I try to follow all suggestions I get the "cannot open the specified file" error but if I go to ECRMDATA sheet and click on the hyperlink it takes me to the redirected website correctly.

Here is my formula in cell A1 of the DATA sheet.

=HYPERLINK(VLOOKUP(DATA!$AO$3&'AD CODING'!$B3&'AD CODING'!C3,ECRMDATA!$A$2:$AW$8500,5,0))

DATA cell A! result is :Front (1) which is what is the vlookup result from column % of the ECRMDATA sheet has the hyperlink that will launch successfully to the redirected website.

Here is the URL that is hyperlinked into the ECRMDATA sheet cell that I am trying to lookup:

ECRM® Data

Any help would be greatly appreciated.
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

gmooney

Board Regular
Joined
Oct 21, 2004
Messages
168
I'm not sure why when I pasted that into this forum is showed the ECRM Data thing because it was an actual URL website address....Am I still doing something wrong?

I tried your formula and I am getting an #VALUE! error now?
 
Last edited:

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
The forum software converts hyperlinks by default.

Well that's much easier of if the cell you look up has the URL to be followed - you won't need a UDF to do that.

When I do a quick test with your URL in cell E1, the formula below follows the hyperlink to that site.

=HYPERLINK(E1,"Click me")

Try that first, then test whether your VLOOKUP by itself returns your URL exactly.
=VLOOKUP(DATA!$AO$3&'AD CODING'!$B3&'AD CODING'!C3,ECRMDATA!$A$2:$AW$8500,5,0))

I'm not able to test that without knowing the values in the referenced cells.
 

gmooney

Board Regular
Joined
Oct 21, 2004
Messages
168

ADVERTISEMENT

Trying to test it with the =HYPERLINK(E1,"Click me") gives me the same error about cannot open the file specified?

 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Did you paste the URL into E1 first?

If you paste that same URL into your browser address window does that go to the correct site?
 

gmooney

Board Regular
Joined
Oct 21, 2004
Messages
168

ADVERTISEMENT

Yes when I copy and paste the URL into a browser window it works.
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
But the hyperlink formula...
=HYPERLINK(E1,"Click me")
doesn't work when the same URL is pasted as text into E1? :confused:

Excel might be modifying the value of cell in the process of converting the URL to a hyperlink.

In AutoCorrect Options, turn off the option that converts Internet and Network paths to hyperlinks. Then try pasting your URL in E1 again and retesting.

EDIT: I modified above suggestion to prevent converting URL in E1 to hyperlink.
 
Last edited:

gmooney

Board Regular
Joined
Oct 21, 2004
Messages
168
The contents of cell E1 is simply a hyperlink that says "AD Details". When I click on the hpyerlink in cell E1 it takes me to the website but the hyperlink formula HYPERLINK(E1,"Click me") gives me the "cannot open the specified file" error
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
A HYPERLINK function will try to follow the value of the referenced cell, so what it's currently doing is trying to go to the URL "AD Details" which won't work.

If you can display your entire URL in the looked up cell in Column E of Sheet ECRMDATA, that would be the easiest solution....
"http://adcompare.marketgate.com/adcomp/Redirect.aspx?p=AdDetails.aspx?adc_i=110153223"

If you need to display a friendly name like AD Details, then you will need to use something like the UDF in the thread I referenced to read the URL from that cell's hyperlink.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,509
Messages
5,523,312
Members
409,511
Latest member
hitesh222002

This Week's Hot Topics

Top