Preserving a hyperlink with index and match

[salwani]

Hi All!

This is my first post. I'm sorry if I've posted this incorrectly in any way.

I am working on an excel workbook. And I am trying to pull a hyperlinked text via index and match from sheet 2 in my workbook to sheet 1.
Sheet 2 has columns with hyperlinks for the rows in each column. And I want it to show the hyperlink from my sheet 2 into this current sheet that I am working in (sheet 1)

Instead it shows this: #Value!

I have used this:
Function GetHyperLink(r As Range) As String
If r.Hyperlinks.Count Then
GetHyperLink = r.Hyperlinks(2).Address
End If
End Function

As a module

And then this: =IF(INDEX(Sheet2!A2:D2,,MATCH($D$8,Sheet2!$A$1:$D$1,0))="","",HYPERLINK(GetHyperLink(INDEX(Sheet2!A2:D2,,MATCH($D$8,Sheet2!$A$1:$D$1,0)))))
as the formula

I don't know exactly where I am wrong in this, I've searched through so many forums but can't seem to figure it out.

Any help would be greatly appreciated!

 

[Anthony47]

I suggest that you split your long formula in small components, so that you will be able to easily understand what is going wrong.

Or try executing step-by-step the formula, using Menu /Formulas /Evaluate formula

Bye