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 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.
Use the HYPERLINK function to create links to workbook locations, pages on the internet, or to files on network servers.
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.
To link to a valid email address in A1, you can concatenate "mailto:" like this:
=HYPERLINK("mailto:"&A1,"email")// link to email address in A1
With two email addresses in A1 and A2, you can create a link like this:
=HYPERLINK("mailto:"&A1&","&B1,"email")// two emails
This formula example explains how to construct a more complete mailto email link with cc, subject, body, etc.
Link_location should be supplied as a text string in quotation marks or a cell reference that contains the link path as text.
If friendly_name is not supplied, the HYPERLINK will display link_location as the friendly_name.
To select a cell that contains HYPERLINK without jumping to the destination, use arrow keys to select the cell. Alternatively, click on the cell and hold the mouse button down until the cursor changes.
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...
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...