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

[gmooney]

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.

 

[Jerry Sullivan]

Hi gmooney,

The VLOOKUP formula just returns the Text Value "ECRM® Data" to the HYPERLINK formula. It doesn't return the actual hyperlink or its target address.

This thread shows a workaround using a VBA User Defined Function....
http://www.mrexcel.com/forum/excel-questions/806123-hyperlink-formula-cell-data.html

 

[gmooney]

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?

 

[Jerry Sullivan]

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]

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

 

[Jerry Sullivan]

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]

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

 

[Jerry Sullivan]

But the hyperlink formula...
=HYPERLINK(E1,"Click me")
doesn't work when the same URL is pasted as text into E1?

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.

 

[gmooney]

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]

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.