Search Functions‎ > ‎

3D HYPERLINK

It's possible to use the HYPERLINK() formula to link to a random address the same way a VLOOKUP() or INDEX(MATCH()) can retrieve information from random locations.

  1. Hyperlink on same sheet:

    =HYPERLINK("#" & ADDRESS(MATCH(A1, B1:B5, 0), 2), "Link")

  2. Hyperlink on a different sheet:

    =HYPERLINK("#'My Database'!" & ADDRESS(MATCH($A1, 'My Database'!$A:$A, 0), 1), "Link")

  3. Hyperlink to a sheet listed in a cell

    =HYPERLINK("#'" & A1 & "'!A1", "Link")

  4. Hyperlink to a random position in a column that must be found on a sheet listed in a cell, a 3D INDEX/MATCH/Hyperlink:

    =HYPERLINK("#" & CELL("address", INDEX(INDIRECT(C3 & "!A:A"), MATCH(A1, INDIRECT(C3 & "!A:A"), 0))), "Link")
                Sample File -    3D VLOOKUP & HYPERLINK.xls

            See this page for more information: 3D VLOOKUP()



Comments