Hyperlink, Index, and Match Formula

[oboegal21]

Hello,

I am hoping to ask some assistance in a formula to bring over a hyperlink from the cell that I am using the INDEX/MATCH formula to bring over.

My current formula:

=IF(ISNA(INDEX(Edit,MATCH(A14,Name,0))),"",INDEX(Edit,MATCH(S15,Name,0)))

Edit and Name are both defined names in the Name Manager of the workbook.

How would I incorporate a HYPERLINK formula to bring over the hyperlink that is in the cell that the INDEX/MATCH formula is grabbing?

Also, in case it makes a difference, the link in the cells that the INDEX/MATCH formula is grabbing is a link to another location within the workbook.

Thank you for your assistance!

[6StringJazzer]

If the hyperlink is an underlying link and the displayed text is not the link itself, then you can't do it with a formula; you have to use a macro.

If the hyperlink is the actual text of the cell then you can do this:

=IF(ISNA(INDEX(Edit,MATCH(A14,Name,0))),"",HYPERLINK(INDEX(Edit,MATCH(S15,Name,0))))

[oboegal21]

Thank you very much for your response! And glad to see a fellow musician on here :-)

I have attached an example of what I am looking for. I tried your suggested formula and it said that the destination couldn't be located. If a macro is involved, would you have knowledge of what this macro would be?

Thank you for your help!

[6StringJazzer]

I have been experimenting with this for a few days but have not been able to figure out how to implement a UDF that will provide a link at the level of indirection you are using. That is, you are linking to cell that in turn has a hyperlink, and that hyperlink references a named range.

Not saying it can't be done, just that I haven't been able to figure it out in a limited amount of time.