So just combining a few tutorials and code from the net here. Basically, this allows you to do a vlookup, or index match, to grab a cell’s value WITH the hyperlink. You may have found this because you realised after doing a vlookup on a table with links, that they won’t come through normally.
So to turn our values into hyperlinks we use the =hyperlink(link_location, [friendly name]) function.
Link location: the URL or document address
Friendly name: the text for the link.
We’ve already got our text for the link right, but we need the actual URL.
So we need to extract that from the original cell, which has to be done using VBA. Don’t be scared, you just need to copy paste the code below into the VBA editor (hit Alt+F11).
Function HLink(rng As Range) As String
'extract URL from hyperlink
'posted by Rick Rothstein
If rng(1).Hyperlinks.Count Then HLink = rng.Hyperlinks(1).Address
This is cool because it gives us a custom function to extract the hyperlink from the cell.
so we can put in a separate cell =Hlink(our cell with the link)
Then we can use a vlookup combo!
=HYPERLINK(vlookup,lookup_value, table_array, column with hyerperlink, exact match), vlookup(lookup_value,table_array,column with hyperlink value, exact match))
We can use index match to avoid creating another cell / column with the URL.
=Hyperlink(Hlink(index match to grab cell),index match to grab cell))
For some reason combining Hlink in a vlookup won’t work.