Solution to preserve Hyperlinks from a VLookUp formula

Hello Excelers,

I have two workbooks. I'm using a vlookup formula in column A in book1 to try and pull hyperlinks from book2. The links in book2 access a website and the cells are text - not the actual URL, path, etc. My problem is i get the formula to grab the text but attempting to access the links results in the error: "cannot open the specified file". What is a way that i can access those hyperlinks?? I have excel 2013, standard.

Thank you for your time.

Attachment 401970

-Austin

=HYPERLINK("#"&VLOOKUP(yourformulagoeshere))

Try this. Since you didn't post an Excel sheet, it's a bit rough-and-ready; but hopefully you'll figure it out!!

Thanks for the quick reply Glenn,

I will give that a try and edit this post when I have figured if it will work or not. I have now attached a sample file. I'm half expecting this solution to turn into a macro or similar. :P

EDIT: So this is not quite what I need. This solution results in an error: "Reference is not valid". Keep in mind: the links I am "grabbing" are pointing to a website, but the cell text itself is a placeholder text, not the URL. I want to pull those cells into another worksheet but still retain the web links. I am not trying to link to another worksheet or workbook.

hyperlinkHelp.xlsx