A solution to the annoying problem with the HYPERLINK function

Is Excel for the web’s calculation engine more advanced than its desktop counterpart?

The other day, I identified a gaping problem with the HYPERLINK function.

After trying to return a table-based column of URLs as a dynamic array, its friendly_name argument was found to be incapable of handling ranges, as it’d repeat the first website name for every value in the spilled result.

Although a workaround involving the implicit intersection operator (@) was suggested, this turned out to be flawed after showing initial promise.

In pursuit of a concrete solution, I eventually found one — but there’s a catch. It only works in Excel for the web.

=FILTER(
MAP(
tblWebsites[URL],
tblWebsites[Name],
LAMBDA(url,name,HYPERLINK(url,name))
),
tblWebsites[Category]=G2
)

This formula returns all website names in tblWebsites that come under the ‘Social Media Platforms’ category (G2), with their respective URLs embedded.

When hovering over each hyperlinked name, a tooltip appears showing the URL that opens in a web browser when clicked on.

Notice how Excel for desktop repeats the first URL for each cell; on the other hand, Excel for the web respects each one.

Not gonna lie — this sucks. However, it does give further weight to our suspicions that Excel for the web is a guinea pig for new features, functions, and minor alterations. That’s why it gets so much of the new stuff first.

Have a look at the video to see what I mean. And by the way, my Excel for desktop version is 2307 (Beta Channel).