Dynamic Cell Hyperlink: Locate row based on cell data Excel help response (12 Jan 2010)

This can easily be done with a macro; however it is also possible to do without a macro at all.

Taking your example, the following spreadsheet shows the solution using formulas.

Excel Spreadsheet:

	A	B
1	4	1
2	=MATCH(A1,B1:B5,0)	2
3	=ADDRESS(A2,2)	3
4	=HYPERLINK("#"&A3,"Go to row")	4
5		5

Cell A1 is the value for which row that we desire to navigate to. The values to match are in column B (only 5 rows are used here for illustration purposes.

Cell A2 finds the index number of column B by matching the value in cell A1 using the MATCH function. The value is 4 as the number 4 is the fourth row in column B. The last parameter is set to 0 to find the first match. Setting it to 1 will find the last match.

Cell A3 then gets the cell address of the matching row number and column B (2) using the ADDRESS function. The value is $B$4.

Finally, cell A4 creates a hyperlink to the address using the HYPERLINK function. This creates a link in the cell and when clicked goes to the address value in cell A3 - in this case B4. Note that when creating this formula, we need to add the "#" symbol to indicate that the destination is within the same worksheet (in the same way as defining an HTML bookmark link).

Combining all of the formulas, we can simply create the dynamic cell hyperlink as one formula in cell A2 as follows:

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

Dynamic cell hyperlink

Excel Help for Dynamic Cell Hyperlink in Planning and Managing Projects

Dynamic Cell Hyperlink