Excel formula: Hyperlink to first match

Generic formula

=HYPERLINK("#"&CELL("address",INDEX(data,MATCH(val,data,0))),val)

Summary

To create hyperlinks to the first match in a lookup, you can use a formula based on the HYPERLINK function, with help from CELL, INDEX and MATCH.

In the example shown, the formula in C5 is:

=HYPERLINK("#"&CELL("address",INDEX(data,MATCH(B5,data,0))),B5)

This formula generates a working hyperlink to the first match found of the lookup value in the named range "data".

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 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.

Author

Dave Bruns

Related formulas

Build hyperlink with VLOOKUP

The hyperlink function allows you to create a working link with a formula. It takes two arguments: link_location and, optionally, friendly_name . Working from the inside out, VLOOKUP looks up and retrieves a link value from column 2 of the named...

Related functions

Excel HYPERLINK Function

The Excel HYPERLINK function returns a hyperlink from a given destination and "friendly name". You can use HYPERLINK to construct a clickable hyperlink with a formula. The HYPERLINK function can build links to workbook locations, pages on the...

Excel 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...

Excel INDEX Function

The Excel INDEX function returns the value at a given position in a range or array. You can use INDEX to retrieve individual values or entire rows and columns. INDEX is often used with the MATCH function, where MATCH locates and feeds a position to...

Excel 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...

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.