To create a hyperlink to VLOOKUP output cell in Google Sheets **is useful if you want to quickly jump to that relevant cell in the lookup table just by clicking the VLOOKUP output cell.**

##### Table of Contents

In our previous article, you’ve learned how to VLOOKUP multiple columns in Google Sheets and now you will learn how to create a hyperlink to `VLOOKUP`

the output cell.

Here’s an example: Let’s say you are a teacher and have a list of all your students with their grades from several subjects. And now, you would want to know what grades have some of your students got at one particular subject. We will use the `VLOOKUP`

function to check the grade each of those students has got 📕✏

But what if now you would also want to check grades from other subjects for those students who have obtained an A at that one particular subject? We will create a hyperlink to `VLOOKUP`

output cell (the grade A at that one particular subject) to quickly jump to that relevant cell (the student’s name) in the lookup table just by clicking the `VLOOKUP`

output cell.

**But how do we do that?**

Simple. The `VLOOKUP`

function needs the **search_key** (the student’s name), **range to search**, and the **index** (column number of the particular subject) to work.

Before showing you how to create a hyperlink to `VLOOKUP`

output cell in Google Sheets, let’s first take a look at the anatomy of the `VLOOKUP`

function.

## The Anatomy of the VLOOKUP Function

The syntax (the way we write) the `VLOOKUP`

function is as follows:

=VLOOKUP(search_key, range, index, [is_sorted])

Let’s break this down to better understand the syntax of the `VLOOKUP`

function and what each of these terms means:

`=`

the equal sign is how we begin any function in Google Sheets.`VLOOKUP()`

is our function. To make it work, we must provide the following attributes – search_key, range, and index.`search_key`

is the value we are searching for within the range.`range`

is the range/array to consider when searching for the search_key.`index`

is the column number of the value to be returned (the first column in the range is numbered ‘1’).`[is_sorted]`

is optional, and it is TRUE by default. It indicates whether the column to be searched is sorted, and is mostly recommended to put FALSE (unsorted).

### ⚠️A few notes you should know when writing your own VLOOKUP function in Google Sheets:

- The
`search_key`

must be from the first column of the range. - Only the first column in the range will be searched. There is a workaround if you want to search other columns.
- If
`[is_sorted]`

is set to**TRUE (sorted)**, the formula will return the nearest match (< or = to the search_key). When all values in the search column are > the search key, the formula will return**#N/A error**. - If
`[is_sorted]`

is set to**FALSE (unsorted)**, the formula will return the exact match. When there are multiple matches, the value from the first found row will be returned. If there is no match, the formula will return**#N/A error**. - To make the cells with no result blank instead of returning the
**#N/A error**, you should simply wrap the`VLOOKUP`

formula with`IFNA`

function.

## How to Create Hyperlink to VLOOKUP Output Cell in Google Sheets

Let’s take a look at the example below and learn how to create a hyperlink to `VLOOKUP`

output cell in Google Sheets, step-by-step.

- Click on the cell
**B15**to make it active. Type the equal sign**‘=’**to start off the function, and start typing the name of the function, which is`VLOOKUP`

. As you begin typing, a box with the auto-suggested functions that start with ‘V’ will pop-up. You can select the`VLOOKUP`

function by clicking on it, just make sure you click on the right one or close it and ignore it.

- Then, we will need the
**search_key**(the value we are searching for within the range). In this example, we will use the names of our male students**(Jordan – A6, Marc – A8, Steven – A10)**as our**search_key**. After the opening bracket, enter our first**search_key**, which is**A6**.

- Then, we will need the range to consider when searching for the
**search_key**. Enter a comma**‘,’**to act as a separator, and enter our range, which is**A3:H10**.

- And finally, we will need the
**index**(the column number of the value to be returned). Since the first column in the range is numbered**‘1’**, the column of the value to be returned (grade at the art class) will be**‘7’**. Enter a comma**‘,’**and enter number**‘7’**.

- Since our column to be searched is sorted, we will enter a ‘
**,**‘ and**TRUE**. To close the function, enter the closing bracket**‘)‘**or hit the**Enter**key on your keyboard. The result in cell**B15**should be**C**.

- This is the formula we will use to
`VLOOKUP`

Jordan’s grade. We will have to change the**search_key**for Marc and Steven. For Marc paste**=VLOOKUP(A8,A3:H10,7,TRUE)**in cell**B16**and for Steven paste**=VLOOKUP(A10,A3:H10,7,TRUE****)**in cell**B17**.

- Now, when we know which of our male students has an A at the Art class, we will have to create a hyperlink to the
`VLOOKUP`

output cell so we can quickly jump to that relevant cell in the lookup table and look at his other grades. For this, we will need the cell address of the`VLOOKUP`

output cell.

- To get the cell address, we should enclose the
`VLOOKUP`

formula with the`CELL`

function**=CELL(“address”,VLOOKUP(A8,A3:H10,7,TRUE))**. Click on the cell**C16**and paste the formula.

- This will return the cell address as
**$G$8**. But we only need the cell reference**G8**, without the dollar signs. To remove the dollar signs, we should use the nested`SUBSTITUTE`

formula**=SUBSTITUTE(CELL(“address”,VLOOKUP(A8,A3:H10,7,TRUE)),”$”,””)**. Click on the cell**D16**and paste the formula.

- Now we need to create a
**dynamic URL**in Google Sheets, which will help us to create a**dynamic hyperlink**to`VLOOKUP`

output cell. Right-click on any cell and choose**‘Get link to this cell’**from the list. The link will be copied to the clipboard. Paste the copied link in any blank cell, and you will get a**URL**like this**https://docs.google.com/spreadsheets/d/1e9W3RyVEODxgWFfkdf-RpXkySOUtyjIWRmT2C77Knfg/edit#gid=0&range=A1**

- Remove the part of the link after the equation sign and enclose the
**URL**within double-quotes**“https://docs.google.com/spreadsheets/d/1e9W3RyVEODxgWFfkdf-RpXkySOUtyjIWRmT2C77Knfg/edit#gid=0&range=”**

- Place an ampersand sign
**‘&’**at the end of the link and insert the formula after it**“https://docs.google.com/spreadsheets/d/1e9W3RyVEODxgWFfkdf-RpXkySOUtyjIWRmT2C77Knfg/edit#gid=0&range=”&**

**SUBSTITUTE(**

**CELL(“address”,VLOOKUP(A8,A3:H10,7,TRUE)),”$”,””)**

- Now we have all we need to create a hyperlink to
`VLOOKUP`

output cell in Google Sheets. The syntax (the way we write) the`HYPERLINK`

function is as follows**=HYPERLINK(URL, [link_label]).**

- We will replace the
**URL**with the**URL**we’ve just created, and the**link_label**with our`VLOOKUP`

formula we used to find Marc’s grade at the Art class.**=HYPERLINK(**

**“https://docs.google.com/spreadsheets/d/1e9W3RyVEODxgWFfkdf-RpXkySOUtyjIWRmT2C77Knfg/edit#gid=0&range=”&**

**SUBSTITUTE(**

**CELL(“address”,VLOOKUP(A8,A3:H10,7,FALSE)),”$”,””),**

**VLOOKUP(A8,A3:H10,7,TRUE)**

**)**

- Click on the cell
**B15**and paste the above formula instead of the`VLOOKUP`

formula that is already there. Marc’s grade at the Art class will now be hyperlinked.

- You can now click on it and quickly jump to that relevant cell in the lookup table.

You can give it a try yourself by making a copy of the spreadsheet using the link below:

You can now use VLOOKUP function together with the **other Google Sheets formulas** to create even more powerful formulas that will help you sort and filter your data. 🙂

## 1 comment

Hi! I cant access to the file