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.

  • lori_m's avatar
    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's avatar
      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's avatar
        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#)

Resources