Excel’s HYPERLINK function is not easy to use. You need to know a few of its secrets to get it to do what you want. Unfortunately, you can’t replicate the HYPERLINK function in a custom function, so we can’t improve it. But the next best thing is to simplify creating the reference you need as the first argument in the HYPERLINK function.

In the image below the formula in cell C2 is displayed in cell D2.

As you can see the reference you need to create combines the sheet name and the cell reference and is reasonably complex.

You use the hash symbol, the inverted comma, the exclamation mark plus the & symbols.

The custom function that I have created called fnHYPERREF simplifies this text string creation, so that you only need a sheet name and a cell or range reference.

The LAMBDA function and its description is shown in the image below.

The LAMBDA formula is shown below.

=LAMBDA(sheet,ref,"#'"&sheet&"'!"&ref)

This custom function simplifies the use of the HYPERLINK function.

It makes it easier to create flexible hyperlinks based on formulas do searches or lookups.