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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,147
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,173
Members
417,129
Latest member
geekzilla

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