Today I´ll show you how to search a table column and jump to that table cell using the hyperlink function. When you click the cell that contains the HYPERLINK function, Excel goes to that location in the table.

Formula in cell B2:

=HYPERLINK("[Find values quickly.xlsx]Table!"&ADDRESS(MATCH(A2, Table1[Number], 0)+MIN(ROW(Table1[Number]))-1, 4), "Go to "&ADDRESS(MATCH(A2, Table1[Number], 0)+MIN(ROW(Table1[Number]))-1, 4))

Explaining formula in cell B2

Step 1 - Return the relative position of an item in an array that matches a specified value

MATCH(A2, Table1[Number], 0)

becomes

MATCH(0.260463529541505, {0.129000449044708;0.440537695535749;0.532039509437168; ...}, 0)

and returns 39.

Step 2 - Create a cell reference as text

ADDRESS(MATCH(A2, Table1[Number], 0)+MIN(ROW(Table1[Number]))-1, 4)

becomes

ADDRESS(39+MIN(ROW(Table1[Number]))-1, 4)

becomes

ADDRESS(39+MIN(3)-1, 4)

becomes

ADDRESS(41, 4)

and returns $D$41

Step 3 - Create a shortcut to a cell in sheet Table

HYPERLINK("[Find values quickly.xlsx]Table!"&ADDRESS(MATCH(A2, Table1[Number], 0)+MIN(ROW(Table1[Number]))-1, 4), "Go to "&ADDRESS(MATCH(A2, Table1[Number], 0)+MIN(ROW(Table1[Number]))-1, 4))

becomes

=HYPERLINK("[Find values quickly.xlsx]Table!$D$41, "Go to $D$41")

What if there are multiple values matching your lookup value?

(Don´t ask me why the underline and link color disappeared in the hyperlink array formulas.)

Array formula in cell B2:

=IFERROR(HYPERLINK("[Find values quickly.xlsx]Table!"&ADDRESS(SMALL(IF($A2=Table1[Number], ROW(Table1[Number]), ""), COLUMN(A1)), 4), "Go to "&ADDRESS(SMALL(IF($A2=Table1[Number], ROW(Table1[Number]), ""), COLUMN(A1)), 4)), "")

How to create an array formula

  1. Copy above array formula
  2. Select cell B2
  3. Click in formula bar
  4. Paste array formula
  5. Press and hold Ctrl + Shift
  6. Press Enter

How to copy array formula

  1. Select cell B2
  2. Copy cell (Ctrl + c)
  3. Select cell range C2:E2
  4. Paste (Ctrl + v)
  5. Select cell range B2:E2
  6. Copy (Ctrl + c)
  7. Select cell range B3:E7
  8. Paste (Ctrl + v)

Explaining formula in cell B2

Step 1 - Check if a condition is met and return corresponding row value if TRUE

IF($A2=Table1[Number], ROW(Table1[Number]), "")

becomes

IF(0.260463529541505={0.129000449044708;0.440537695535749;0.532039509437168; ...}, {3, 4, 5, ...}, "")

and returns

{""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; 41; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; 261; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""}

Step 2 - Return the k-th smallest value

SMALL(arrayk)

SMALL(IF($A2=Table1[Number], ROW(Table1[Number]), ""), COLUMN(A1))

becomes

SMALL({""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; 41; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; 261; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""}, 1)

and returns 41.

Step 3 - Create a shortcut to a cell in sheet Table

HYPERLINK("[Find values quickly.xlsx]Table!"&ADDRESS(SMALL(IF($A2=Table1[Number], ROW(Table1[Number]), ""), COLUMN(A1)), 4), "Go to "&ADDRESS(SMALL(IF($A2=Table1[Number], ROW(Table1[Number]), ""), COLUMN(A1)), 4))

returns

HYPERLINK("[Find values quickly.xlsx]Table!$D$41, "Go to $D$41)

Download excel *.xlsx file

Find values quickly.xlsx