Need help getting a Hyperlink to appear in vlookup

**nutathis** · 02-02-2012, 03:03 PM

Hi, I created a vlookup that pulls data from two tabs within my workbook and the results have hyperlinks that are not appearing in the vlookup. The hyperlinks take you to another sheet in the workbook but only appear as text within the vlookup results. I'm attaching my workbook and would appreciatate any help on this. I tried using the =hyperlink befre vlookup but that made everything that appears in the cell a hyperlink even if it's not. I've also tried =gethyperlink in VBA but it did not work.

Thanks in advance.

**nutathis** · 02-03-2012, 12:15 PM

Any suggestions? Or ideas?

Originally Posted by nutathis

Hi, I created a vlookup that pulls data from two tabs within my workbook and the results have hyperlinks that are not appearing in the vlookup. The hyperlinks take you to another sheet in the workbook but only appear as text within the vlookup results. I'm attaching my workbook and would appreciatate any help on this. I tried using the =hyperlink befre vlookup but that made everything that appears in the cell a hyperlink even if it's not. I've also tried =gethyperlink in VBA but it did not work.

Thanks in advance.

**Haseeb A** · 02-03-2012, 05:43 PM

Try this,

Insert this VBA in a module,

Function HyperLinkText(pRange As Range) As String

        Dim ST1 As String
        Dim ST2 As String

        If pRange.Hyperlinks.Count = 0 Then
           Exit Function
        End If

        ST1 = pRange.Hyperlinks(1).Address
        ST2 = pRange.Hyperlinks(1).SubAddress

        If ST2 <> "" Then
           ST1 = "[" & ST1 & "]" & ST2
        End If

        HyperLinkText = ST1

End Function

Then C8, copy to C10,

=HYPERLINK("#'"&SUBSTITUTE(MID(HyperLinkText(INDEX(Data1!$C$3:$E$9,MATCH(A$1,Data1!A$3:A$9,0),ROWS(C $8:C8))),3,250),"!","'!"),INDEX(Data1!$C$3:$E$9,MATCH(A$1,Data1!A$3:A$9,0),ROWS(C$8:C8)))

C13 to down.

=HYPERLINK("#'"&SUBSTITUTE(MID(HyperLinkText(INDEX(Data2!$C$3:$H$9,MATCH(A$1,Data2!A$3:A$9,0),ROWS(C $13:C13))),3,250),"!","'!"),INDEX(Data2!$C$3:$H$9,MATCH(A$1,Data2!A$3:A$9,0),ROWS(C$13:C13)))

VBA from this site,

http://www.techonthenet.com/excel/macros/extract_hl.php