Explanation
Working from the inside out, we use a standard INDEX and MATCH function to locate the first match of lookup values in column B:
INDEX(data,MATCH(B5,data,0))
The MATCH function gets the position of the value in B5 inside the named range data, which for the lookup value "blue" is 3. This result goes into the INDEX function as row_num, with "data" as the array:
INDEX(data,3)
This appears to return the value "blue" but in fact the INDEX function returns the address E6. We extract this address using the CELL function, which is concatenated to the "#" character:
=HYPERLINK("#"&CELL(E6,B5)
In this end, this is what goes into the HYPERLINK function:
=HYPERLINK("#$E$6","blue")
The HYPERLINK function then constructs a clickable link to cell E6 on the same sheet, with "blue" as the link text.
Related formulas
Build hyperlink with VLOOKUP
Related functions
HYPERLINK Function
The Excel HYPERLINK function returns a hyperlink to a given destination. You can use HYPERLINK to create a clickable hyperlink with a formula. The HYPERLINK function can build links to workbook locations, pages on the internet, or files on network servers....
CELL Function
The Excel CELL function returns information about a cell in a worksheet. The type of information to be returned is specified as info_type. CELL can get things like address and filename, as well as detailed info about the formatting used in the cell. See below for a full list of...
INDEX Function
The Excel INDEX function returns the value at a given location in a range or array. You can use INDEX to retrieve individual values, or entire rows and columns. The MATCH function is often used together with INDEX to provide row and column numbers....
MATCH Function
MATCH is an Excel function used to locate the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching, and wildcards (* ?) for partial matches. Often, MATCH is combined with the...
