Forum Discussion

RonenBitmn's avatar
RonenBitmn
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...
Formulas and Functions
m_tarler's avatar
m_tarler
Bronze Contributor
Apr 28, 2025

So I am having this problem also and this INDEX( [array_of_links], XMATCH( [array], [array] ) ) seemed to be the closest to working for me.  There is another discussion a couple years 'newer' with some interesting approaches but I couldn't get them to work either (hyperlink-function-works-as-flash-fill-but-not-as-dynamicspill-array).  But playing around with this INDEX idea I got the following to work:

=TAKE(HYPERLINK(
             INDEX(HSTACK(
                              "#"&ADDRESS(ROW(A1#),COLUMN(A1#)),
                              B1#),
                         XMATCH(A1#,A1#),
                         {1,2}))
             ,,-1)

So this will link to the cells in A1# using the friendly names in B1#

basically this 'STACKS' the link addresses (row3) with the friendly names (row4)

then it uses the XMATCH trick (row5) to make and array that works with HYPERLINK

row 6 makes INDEX return BOTH the address name and the friendly name but both apparently are linked to the correct cell

row 7 is part of the TAKE to only keep the friendly name column.

I hope this solution may be useful for anyone else having this problem.

Resources