The Excel HYPERLINK function returns a hyperlink from a given destination and link text. 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 to files on network servers.
Create a clickable link.
A clickable hyperlink
=HYPERLINK (link_location, [friendly_name])
link_location - The path to the file or page to be opened.
friendly_name - [optional] The link text to display in a cell.
The HYPERLINK function creates a hyperlink from a given destination and "friendly name", which is simply the link text. You can use HYPERLINK to construct a clickable hyperlink with a formula. The HYPERLINK function can build links to other cells in a workbook, other sheets, named ranges, other workbooks, pages on the internet, or to files on network servers. You can also use HYPERLINK to create email links.
The HYPERLINK function takes two arguments: link_location and friendly_name. Link_location is the destination or path the link should follow, entered as text. Friendly_name is the text that will be displayed with the link.
When a user clicks a cell that contains the HYPERLINK function, Excel will open the file or page specified by link_location. Link_location can be a cell reference or named range, a path to a file stored on a local drive, a path a file on a server using Universal Naming Convention (UNC), or an internet path in Uniform Resource Locator (URL) format.
Example #1 - link to cell
To link to another cell in the same worksheet, prefix the cell with "#":
=HYPERLINK("#Z100","link to Z100")// cell in same sheet
Example #2 - link to sheet
To link to another sheet in the same workbook, use "#" with the Sheet name like this
=HYPERLINK("#Sheet2!A1","Sheet2")// sheet2 in same workbook
If the sheet name contains a space, you'll get an invalid reference error with the formula above. In that case, you'll need to enclose the sheet name in single quotes (') like this:
=HYPERLINK("#'Sheet 2'!A1","Sheet 2")// sheet name with space
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...
Working from the inside out, we use MATCH to locate the relative position of the last entry in column C:
Basically, we are giving match a "big number" it will never find in approximate match...
Working from the inside out, we use a standard INDEX and MATCH function to locate the first match of lookup values in column B:
The MATCH function gets the position of the value in B5...