Vlookup with hyperlink

[hcabs99]

Hi

I'm trying to do a vlookup to return a hyperlink.

Have done this formula - =HYPERLINK(VLOOKUP($C$8,'Linked Items'!A:H,2,FALSE))

However my excel file is stored on sharepoint and for some reason the hyperlink is putting the sharepoint link as a prefix to the desired hyperlink which makes the address incorrect. Any idea how i can get to just return the hyperlink address from the cell it returns?

 

[hcabs99]

Correction to my statement. Actually the issue i have is , i have put a "friendly" name in the source sheet and the hyperlink generated when i do the lookup is trying to find the address with the friendly name rather than the actual link in the source sheet.

If i try without renaming the source file , i get a #value! in the cell

Any ideas would be most helpful!