VLookup return Hyperlink information

Jon Carlson
Jon Carlson used Ask the Experts™
on
I am using a VLOOKUP statement and 1 of the columns in the original data contains a Hyperlink, can I carry this hyperlink over into the new cell withouth using a vba type command?  I would like to be able to click on the hyperlink from the lookup value inserted into the new cell.

Thank you,
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NorieAnalyst Assistant

Commented:
You could wrap your VLOOKUP formula for that column in the HYPERLINK worksheet function.

=HYPERLINK(VLOOKUP(A1, Sheet1!A1:D100, 3,0))

Author

Commented:
Thank you for the suggestion. I tried that and it didn't quite work. The Hyperlink that is carried over looks to point to a file rather than a URL. I have attached a file to show what I am seeing. TEST-JWC-2019.xlsx
NorieAnalyst Assistant

Commented:
Did you create the hyperlinks using Insert Hyperlink...?

Author

Commented:
No, I copied them from a web page and pasted them in,  they work when copied between cells just not when referenced through a formula.  

I can explore using the insert method,  it's just that the list changes frequently and I will have the manually insert.  

So are you saying I can't make this work through the copy/ paste method to insert into the spreadsheet?

What about if I use vba to copy and paste through the vlookup, can I carry the properties that way?
Analyst Assistant
Commented:
The hyperlinks are a property of the cell(s) they are in and you can't use a formula to copy them to another sheet.

You could use VBA to do it, perhaps using a worksheet change event.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial