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.
Explanation
The ROW function, when entered into a cell with no arguments returns the row number of that cell. In this case, the first instance of the formula is in cell D6 so, ROW() returns 6 inside the formula in D6.
We want to start with 1, however, so we need to subtract 5, which yields 1.
As the formula is copied down column D, ROW() keeps returning the current row number, and we keep subtracting 5 to “normalize” the result back to a 1-based scale:
=$B$6*1 // D6
=$B$6*2 // D7
=$B$6*3 // D8
etc
If you are copying a formula across columns, you can use the COLUMN function the same way.
Note that you are free to use the result of COLUMN or ROW any way you like in the formula. Both functions return a number, so you can apply them in a formula them just like you would use any number.
