VLookup result not clickable hyperlink

**CHoedeman** · 02-16-2011, 07:28 AM

Hello folks,

I have a issue here, that I coudn't find solved on this site, correct me if wrong.
I have a table in sheet (1) with these codes and hyperlinks:
A B
1011 Hyperlink1
1010 Hyperlink2
1001 Hyperlink3
1000 Hyperlink4
1111 Hyperlink5
1110 Hyperlink6
1101 Hyperlink7
1100 Hyperlink8
This is one of my sheets.

Now I have another sheet (2) with cells resulting in one of the codes at A. Then I have another cell in sheet (2) vlooking up the value in the table on sheet (1). No problems with the Vlooking up or the Hyperlinks in sheet (1) but then the resulted Hyperlink in sheet (2) is not clickable. Does someone have a solution for this?

Thanks in advance and excuse me for my English.

Yours,
Christian Hoedeman
the Netherlands

**Domski** · 02-16-2011, 07:43 AM

Vlookup doesn't return anything more than the value it finds so formatting etc is excluded. You would need to enclose the vlookup in a hyperlink function.

Dom

**CHoedeman** · 02-16-2011, 08:26 AM

Thanks for your quick response!
How exactly do I include a Vlookup formula into a Hyperlink?

**DonkeyOte** · 02-16-2011, 08:28 AM

If the text to display of the Hyperlink on Sheet1 is the hyperlink address itself then you can use:

Please Login or Register  to view this content.

If not the above won't work as VLOOKUP will only return the text being displayed in the hyperlink cell

In other words - using a much simplified example:

Please Login or Register  to view this content.

**CHoedeman** · 02-16-2011, 08:42 AM

Thanks for your response Donkey,

I feel that I'm one step closer to perfect result. I'm getting the error now that it can't open the specific file.

Maybe it's easier if I upload the file:
The sheet 'data' contains the specific codes and to which path it should be directed. Column E and F. The Sheet 2 should be the sheet that has to be filled in. When everything is filled in it creates a code, and by this code the correct hyperlink has to be found in sheet 'data' and to be showed in column G.
Can someone give me the corect formula for column G of sheet 2 to get for example code (F) 1011 to path C:\test?

F = samengevoegd = the code

Thanks in advance,

EDIT: It worked out! I know what I did wrong and now it works perfectly! Thank you guys for the help!

**Domski** · 02-16-2011, 08:54 AM

You need to make the reference to the table array in your vlookup absolute:

=HYPERLINK(VLOOKUP(F2,data!$E$2:$F$9,2,0))

As long as the text displayed in column F of your lookup table is a valid file/folder path or web address the hyperlink should work fine.

When I tested your example the folder C:\temp opened fine for me.

Dom