EXCEL : Hyperlinked dynamic index
Solution 1:
This method will help you to create Hyperlink for the list of extracted/Indexed vales:
How it works:
- This lookup and creates hyperlink to the first match name.
-
An array (CSE) formula in cell
F63:{=IFERROR(HYPERLINK("#"&CELL("address", INDEX($C$63:$C$67, MATCH($E63,$A$63:$A$67,0))), INDEX($C$63:$C$67, MATCH($E63,$A$63:$A$67,0))),"")}
::Edited::
Caveat:
Below shown method works with all versions of EXCEL,, originally OP is using Google Online Version and the OP bring it in the notice few minutes back only. So readers consider it for Excel only.
-
Write this array (CSE) formula in Sheet1 Cell
L1.{=IFERROR(HYPERLINK("#"&CELL("address",INDEX(Sheet2!$I$1:$I$5,SMALL(IF(Sheet2!$I$1:$I$5<>"",ROW(Sheet2!$I$1:$I$5)),ROWS(Sheet2!$A1:$A$1)))),INDEX(Sheet2!$I$1:$I$5,SMALL(IF(Sheet2!$I$1:$I$5<>"",ROW(Sheet2!$I$1:$I$5)),ROWS(Sheet2!$A1:$A$1)))),"")}
N.B.
- Finish an array (CSE) formula with Ctrl+Shift+Enter & fill down.
- Adjust cell references in the formula as needed.