Forum Discussion

Copper Contributor

Jan 04, 2021

Hyperlink function do not support Dynamic Arrays

Hi, tried a very simple sheet where cell a1..a3 are links and cells b1..b3 are friendly names the formula hyperlink(a1:a3,b1:b3) shows the hyperlinks correctly but not the friendly names anyone ha...

HyperlinkDynamicArrayIssue.xlsx10 KB

Formulas and Functions

lori_m

Iron Contributor

May 07, 2025

m_tarler

Thanks for sharing - no way I'd ever have thought of that! This INDEX method seems to provide a generic approach for spilling a HYPERLINK formula with column arrays in both arguments. To improve efficiency I guess you could replace the XMATCH part with a range id column (C1#).

My approach would have been to make the link_location parameter relative to the current row, e.g.

=HYPERLINK("#"&ADDRESS(ROW(),COLUMN(A1#)),B1#)
=HYPERLINK("#r[0]c"&COLUMN(A1#), B1#)

These both look ok on office desktop but the second one seems to have an issue on web version.

m_tarler

Bronze Contributor

May 07, 2025

I will have to remember to also test on the web version.

As for the XMATCH that seemed to be part of the 'trick' to make it work but I will have to try the ROW(). In my actual case it was even more complicated because I wanted a TRANSPOSE to also happen so the link spill was in a row while the link targets was in a column.

lori_m
Iron Contributor
May 07, 2025
As per latest response, I think you can try entering in e.g. H2,
=HYPERLINK(INDEX("#"&ADDRESS(ROW(A1#),COLUMN(A1#)),ROW()-ROW(H2)+1,1),B1#)