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>>, " ")