Search code examples
excelexcel-formula

Is it possible for HYPERLINK to spill friendly names?

I have a table of websites, with each row containing its name and URL. I'm trying to use the HYPERLINK() function to return these in a spilled dynamic array.

=HYPERLINK(tblWebsites[URL],tblWebsites[Website])

enter image description here

Unfortunately, using the [friendly_name] argument repeats the first entry, meaning it's displaying 'Google' three times, rather than 'Yahoo' and 'Bing' for the second and third values.

I don't want to use one formula per value, so is there some kind of obscure workaround for this?

Solution

  • You can use the @-notation when you use it inside the Excel Table as follow:

    =HYPERLINK(@[URL],@[Website])

    Here is the output: output

    Outside of the table, doesn't work because it seems HYPERLINK function doesn't accept for friendly_name input argument an array, but the link output is correct. For example the following works: =HYPERLINK(tblWebsite[URL]), but it doesn't show the friendly name. You can use MAP function to overcome it:

    =MAP(tblWebsite[URL],tblWebsite[Name], LAMBDA(url,name, HYPERLINK(url, name)))

    Here is the output in E column: Map output