hyperlink and vlookup in the same sheet

**andrew_aweb** · 11-03-2009, 06:34 AM

I have a worksheet that contains multiple tabs, I had a table with column A as the names and column B as the text with a hyperlink. In another sheet I use vlookup to match the table to return the hyperlink from main table.

= hyperlink("#Sheet2!"&Cell("address" ,b2), "")

Knowing how vlookup and hyperlink formula work, when combining the 2 formula only return a text field which does not pull out the hyperlink from the table.

I search online many times without a soln. I know soln for hyperlink to website, eg (www...) but it becomes complicate if I want to link to cell from the same workbook.

Some online soln, suggest to put the table hyperlink as C:\Documents and Settings\User\My Documents\File.xls..this is too lengthy and it does not point to the specific sheet and cell, I want.

Any soln ... ?

I am trying to get below formula to work :
=HYPERLINK(vlookup(A1,test<name range>,2,0))," ")
=hyperlink("#"& cell )"address" <<insert vlookup>>, " ")

**DonkeyOte** · 11-03-2009, 06:49 AM

I think you will need to post an example file which illustrates your present setup...

In the meantime however - guessing in terms of interpretation you will need to either:

a) omit friendly link in source table hyperlinks altogether (you have set to Null at present), eg:

B2: =HYPERLINK("#Sheet2!"&CELL("ADDRESS",B2))

at which point you can then use VLOOKUP as intended,eg

result: =HYPERLINK(VLOOKUP(A1,range,2,0),"link")

or

b) set friendly link up in such a way that you can reconstruct the hyperlink from the VLOOKUP result, eg:

B2: =HYPERLINK("#Sheet2!"&CELL("address",B2),CELL("address",B2))

at which point you can use VLOOKUP result in accordance with other "known factors" (relative link, sheet name etc) to create link, eg:

result: =HYPERLINK("#Sheet2!"&VLOOKUP(A1,range,2,0),"link")

In essence the VLOOKUP will only return the friendly name assigned to the HYPERLINK and thus your link can only be recreated with that value (constructed accordingly).

**andrew_aweb** · 11-03-2009, 01:25 PM

I hve come up with a example file in .xls, but I can;t get it post in the forum.

I have attached 2 pix as illustration. I have created a table and name it "TEST" under sheet 1, in column B, is shows the text "axh9830", which in fact is a hyperlink formula " ,=HYPERLINK("#Sheet1!"&CELL("address",B11),"axh9830")"

http://s568.photobucket.com/albums/s...rrent=pix1.jpg

Under Sheet 2, I need to call out using vlookup_value, returning the Actual hyperlink, that is to Sheet1,B11.

http://s568.photobucket.com/albums/s...rrent=pix2.jpg

I tried yr soln (b), it result in "Reference in not valid"
Another issue is that at times the hyperlink may reference to other sheet, instead of Sheet2.

**DonkeyOte** · 11-03-2009, 01:49 PM

Judging by the images I'm not quite sure you understood the point I was making.

The VLOOKUP will only return the friendly_text associated with the hyperlinks (or where omitted the address)
You must therefore ensure that you can reconstruct the hyperlink from the friendly_text value (or if omitted the address!).
If you can not then you can not return the hyperlink to your 2nd sheet via formulae.

You said you could not get my suggestion to work... using your images, the hyperlink for Agito would be:

=HYPERLINK("#Sheet1!B11")
etc

Your formula in 2nd sheet would then be:

=HYPERLINK(VLOOKUP(B3,test,2,0))

Point being by omitting the friendly_name the HYPERLINK will be default use the Address... you can in turn encase the VLOOKUP result (address) in a Hyperlink function.