Vlookup return hyperlink

[SuperBaby]

Hi,

I would like to have a vba code or formula that can vlookup my Sheet1!A2 value in Sheet2!A:A and return the cell which has hyperlink embedded. The hyperlink is linked to the file path. Can you please help?

 

[vds1]

Paste this code in a module,

Function GetHyperLinkAddress(rng As Range) As String
Dim hl As Hyperlink
For Each hl In rng.Parent.Hyperlinks
    If hl.Range.Address = rng.Address Then
        GetHyperLinkAddress = hl.Address
        Exit Function
    End If
Next hl
GetHyperLinkAddress = "Not Found"
End Function

Now goto sheet2 and in cell B2 enter formula as below and drag down.

= GetHyperLinkAddress(A2)

Now in Sheet1 use this formula in cell A2 and drag down

=HYPERLINK(VLOOKUP(A1,Sheet2!A:B,2,0))

HTH

 

[SuperBaby]

Wow, vds1
Highly appreciate your help. Thank you so much.. It works really great!

 

[vds1]

Thank you for your feedback.