Hyperlink To Cell

This content is licensed under The MIT License. See here for more details.

This function creates a hyperlink to a specific cell based on an XLOOKUP.

=LET(
    target,
    XLOOKUP([@<NameColumn>], <TargetTable>[<NameColumn>], <TargetTable>[<TargetColumn>]),
    HYPERLINK(
        CONCAT(
            "#",
            CELL(
                "address",
                INDIRECT(
                    ADDRESS(
                        INDEX(ROW(<SourceTable>), 1) +
                        XMATCH(target, <SourceTable>[<TargetColumn>], 0, 1) - 1,
                        INDEX(COLUMN(<SourceTable>[<TargetColumn>]), 1),
                        1,
                        0,
                        "<SourceTable>"
                    ),
                    FALSE
                )
            )
        ),
        target
    )
)

This will work without the LET function (< Excel 2021), but you’ll need to duplicate the XLOOKUP function.