Preserving a hyperlink with index and match

salwani

New Member
Joined
Mar 25, 2021
Messages
1
Platform
  1. Windows
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!
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,722
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,507
Messages
5,854,131
Members
431,619
Latest member
AjaiSpillers

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top