I was wondering if anyone knows how to return a hyperlink with hlookup? Also to go one step farther and first determine if the lookup value is a hyperlink. I have a user defined function that determines whether a cell is a link but in combination with the hlookup function it always returns an error. Any help would be appreciated.
Return a hyperlink with hlookup
- Thread starter dmfamman
- Start date
AliGW
Administrator
- Joined
- Nov 8, 2015
- Messages
- 1,843
- Reaction score
- 8
- Points
- 38
- Location
- Ipswich, Suffolk, England
- Excel Version(s)
- MS 365 (Beta Insider Channel)
What are the UDF and the formula that you have tried? Share them here, please.
Thanks so much for looking at this. I'm a sort of shade tree mechanic with excel.
Here is the UDF. It will give me the correct TRUE or FALSE response used alone.
Function IsHyperlink(r As Range) As Boolean
IsHyperlink = r.Hyperlinks.Count
End Function
and here is the formula.
=IF(ishyperlink(HLOOKUP($C$3,Combo!$B$3:$AX$213,A5,FALSE)=TRUE),"YES","NO")
Here is the UDF. It will give me the correct TRUE or FALSE response used alone.
Function IsHyperlink(r As Range) As Boolean
IsHyperlink = r.Hyperlinks.Count
End Function
and here is the formula.
=IF(ishyperlink(HLOOKUP($C$3,Combo!$B$3:$AX$213,A5,FALSE)=TRUE),"YES","NO")
- Joined
- Dec 16, 2012
- Messages
- 2,319
- Reaction score
- 40
- Points
- 48
- Excel Version(s)
- 365
HLOOKUP returns a value unfortunately, so try this:
with an additional UDF:
Code:
=IF(ishyperlink(INDEX(Combo!$B$3:$AX$213,A5,MATCH($C$3,Combo!$B$3:$AX$3))),HYPERLINK(hyplnk(INDEX(Combo!$B$3:$AX$213,3,MATCH($C$3,Combo!$B$3:$AX$3))),INDEX(Combo!$B$3:$AX$213,3,MATCH($C$3,Combo!$B$3:$AX$3))),"No")
Code:
Function hyplnk(myCell As Range)
hyplnk = myCell.Hyperlinks(1).Address
End Function