Preserving URL with VLOOKUP where text doesn't match URL

I am attempting to use VLOOKUP to bring over data from another worksheet along with the URL attached to the text.

I tried using the following and it doesn't bring over the url but only the text data which is different from the url.

=HYPERLINK(VLOOKUP(L2,SharepointPhotos!A2:B7014,2,FALSE))

Worksheet 1- AllPhotos
This worksheet is where I am trying to bring over data from SharepointPhotos with a match from column L2 of this worksheet.

Worksheet 1- SharepointPhotos
This worksheet contains the data I would like to bring over to the AllPhotos worksheet. Column A contains what I am matching to L2 of AllPhotos and Column B is what contains the data I need to bring over.

I have also tried adding the following to the standard module along with the following code but it still doesn't work.

Function GetHyperLink(r As Range) As String
If r.Hyperlinks.Count Then
GetHyperLink = r.Hyperlinks(1).Address
End If
End Function

=HYPERLINK(GetHyperLink(INDEX(SharepointPhotos!$B$1:$B$7014,MATCH(L2,SharepointPhotos!$A$1:$A$7014,0))),(VLOOKUP(L2,SharepointPhotos!$A$1:$B$7014,2,FALSE)))

Any suggestions or help would be greatly appreciated.

Thanks.

Thanks for the info. I will attempt to modify the code.