Hyperlink function do not support Dynamic Arrays

For future reference: based on comments below, a method to spill any HYPERLINK formula of the form,

=HYPERLINK(link_location,friendly_name)

is to wrap link_location in an INDEX function:

=HYPERLINK( 
    INDEX(link_location, ROW()-ROW(RC)+1, COLUMN()-COLUMN(RC)+1), 
    friendly_name 
)

Note: this formula is given in RC-notation so that RC refers to the formula cell e.g. G4. In the general case link_location and friendly_name may be 2D calculated arrays and the formula may be freely moved around

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.

RonenBitmn 

Maybe try inserting an @ symbol:

=HYPERLINK(@A1:A3,B1:B3)

To make backward compatible perhaps use a relative name ThisRow:=R (in R1C1 notation):

=HYPERLINK(ThisRow A1:A3,B1:B3)

Note the Function Wizard can provide clues to the behaviour in such cases. Without using an intersection operator one sees arrays in the Friendly name and Result but not Link_Location:

Using bold type for arrays the function could therefore be written as:

HYPERLINK(Link_Location, Friendly_name) 

Entering an array in Link_Location results in a nested array which gets truncated by the calc engine. 

Compare with a function like CONCATENATE which only shows single value results:

CONCATENATE(Text1,[Text2],...)

Then arrays are handled in the normal way.

Not sure why HYPERLINK has this signature, perhaps it was originally designed to allow for text inputs longer than 255 characters in Excel 2003 versions and prior?

RonenBitmn 

...to the fast :))

=hyperlink(a1:a3,b1)

or use Dynamic Hyperlinks

...see the attached file.

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.