I am looking for some help with a VBA solution for preserving hyperlinks when using VLOOKUP on Excel (2010).

I have a load of data on Sheet 1 for internal use only, and a cut-down version of this on Sheet 2. Instead of recreating Sheet 2 everytime, I am looking to have a working version which updates everytime Sheet1 is updated. Thus, I have used VLOOKUP on Sheet 2 so that only the desired info is returned on sheet 2.

However, the problem was that sheet 1 contained in many cells Hyperlinks to external websites, and this would not pull through to Sheet2 using VLOOKUP. With some help, however, using the following VBA solution the hyperlinks now pull through:

Function GetHyperLink(r As Range) As String

If r.Hyperlinks.Count Then

GetHyperLink = r.Hyperlinks(1).Address

End If

End Function

And I am using the following formula in the relevant cell(s) in Sheet2:

=HYPERLINK(GetHyperLink(INDEX('Sheet 1'!$B$1:$B$10001,MATCH(A4,'Sheet 1'!$A$1:$A$10001,0))),(VLOOKUP(A4,'Sheet 1'!$A$1:$B$10001,2,FALSE)))

However, the problem is with formatting: every cell on Sheet2 is formatted blue and underlined, even although some of them do not contain a hyperlink!

Is someone able to help with a VBA solution/formula to fix this last piece of the puzzle?

Many thanks, in anticipation.