Explanation

Working from the inside out, we use MATCH to locate the relative position of the last entry in column C:

MATCH(9.99E+307,C5:C100)

Basically, we are giving MATCH a “big number” it will never find in approximate match mode. In this mode, MATCH will “step back” to the last numeric value.

Note: this works in this case because all values in C are numeric, and there are no blanks. For other situations (text values, etc.), see other “last row” formulas mentioned below. You will need to adjust the MATCH part of the formula to suit your needs.

Next, we use INDEX to get the address of the “entry after the last entry” like this:

INDEX(C5:C100,6))

For the array, we give INDEX C5:C100, which represents the range we care about. For row number, we give INDEX the result returned by MATCH + 1. In this example, this simplifies to:

INDEX(C5:C100,6)

This appears to return the value at C10, but in fact, INDEX returns an address ($C$10), which we extract with the CELL function and concatenate to the “#” character:

=HYPERLINK("#"&CELL($C$10)

In the end, this is what goes into the HYPERLINK function:

=HYPERLINK("#$C$10","First blank")

The HYPERLINK function then constructs a clickable link to cell C10 on the same sheet, with “First blank” as the link text.

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.