Excel Formula / Vlookup / Index Match To Get A Hyperlink

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).

Source: Contextures

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

 

Index 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.

 

Workbook Example

Vlookup with hyperlinks

 

 

This entry was posted in Excel, VBA and tagged , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *