Create Hyperlink to Vlookup Output Cell in Google Sheets

How to create hyperlink to Vlookup output/result cell in Google Sheets?

Vlookup is very flexible in Google Spreadsheets and I have shared different Vlookup variations. Here is a ‘crazy’ Vlookup formula.

I am sharing it with you to help you learn how to create Hyperlink to Vlookup output cell. That means you can click the Vlookup output to jump to that relevant cell in the lookup table. This will be useful in the following scenario.

From ‘Sheet1’, search a key in a table (dataset) in ‘Sheet2’ and return a value from the row found. Click that output/returned value in ‘Sheet1’ to jump to the cell in ‘Sheet2’ where the output value resides.

In this, please note that ‘Sheet1’ and ‘Sheet2’ are in the same Google Sheets file, not two independent Spreadsheets.

The above is applicable within a single sheet also. See one example.

Hyperlink Vlookup:

In this, the Vlookup search_key is in cell G2. The formula in cell H2 searches that key in the first column of the table in the range A1:E and returns the value from the second column (column B) in the found cell. The output is hyperlinked!

Below are the steps involved in building a Hyperlink to Vlookup output cell in Google Sheets.

Steps to Create Hyperlink to Vlookup Output Cell in Google Sheets

Sample Data in ‘Sheet2’:

In ‘Sheet1’ my search key is in cell A1 and that is “II-2053”.

A Vlookup in Cell B1 in that sheet can search down the first column of Sheet2!A1:E for this key and return a value of a specified cell in the row found.

Step # 1:

Vlookup Formula Without Hyperlinking

Formula 1 (Cell B2 in Sheet1):

=VLOOKUP(A1,Sheet2!A1:E,2,FALSE)

If you know how to use the Vlookup in Google Sheets, then you may probably know that this formula will return the value “Gravel 40-60 mm” since the column number 2 is provided in the formula.

What I want to do is to create a Hyperlink to this Vlookup result cell. How to Hyperlink the Vlookup output “Gravel 40-60 mm” to the source cell Sheet2!B5. Find the steps below.

Step # 2:

Find the Cell Address of Vlookup Result in Google Sheets

You have already the Vlookup formula in cell B1 in Sheet1. Now you must first know, how to get the cell address of the Vlookup output.

To get the cell address of the Vlookup output, enclose the Vlookup formula with the Cell function as below.

=cell("address",VLOOKUP(A1,Sheet2!A1:E,2,FALSE))

This will return the cell address Sheet2!$B$5. But I only want the cell reference B5, that without the Sheet name and the dollar sign for our purpose.

Step # 3:

Removing Sheet Name and $ Sign from a Cell Address in Sheets

With a nested Substitute formula we can remove the Sheet name and dollar sign from the cell address that returned by the above formula.

=substitute(
     substitute(
        cell("address",VLOOKUP(A1,Sheet2!A1:E,2,FALSE)),"Sheet2!","")
    ,"$",""
)

Step # 4:

Creating a Dynamic Hyperlink URL in Google Sheets

In the following step, I am going to create a dynamic URL in Google Sheets. The dynamic URL helps us to create a dynamic Hyperlink to Vlookup output cell. Here are those cool steps.

Go to Sheet2 and right-click on ‘Sheet2!A1’ and select “Get link to this cell”.

When you paste the copied link in any blank cell, you will get a URL like this.

https://docs.google.com/spreadsheets/d/1-x9eKz-OF343Pgj44X...41048&range=A1

In this, just replace the A1 that you can see at the end of the URL with the above, nested Substitute, Cell, and Vlookup combo, formula.

How?

Place an ampersand sign in at the end of the copied link and insert the formula. Also, enclose the above URL within double-quotes. Here is that formula.

Formula 2:

"https://docs.google.com/spreadsheets/d/1-x9eKz-OF343Pgj44X...41048&range="&
substitute(
     substitute(
        cell("address",VLOOKUP(A1,Sheet2!A1:E,2,FALSE)),"Sheet2!",""),
     "$",""
)

Hyperlinking Vlookup Output in Google Sheets

Create a Hyperlink formula.

We are now all set to build Hyperlink to Vlookup Output Cell in Google Sheets.

Hyperlink Function Syntax:

=HYPERLINK(URL, [link_label])

Replace URL with the above formula 2 and link_label with the Vlookup Formula 1.

See that final Hyperlinking Vlookup Result formula.

=hyperlink(
     "https://docs.google.com/spreadsheets/d/1-x9eKz-OF343Pgj44X...41048&range="&
        substitute(
           substitute(
           cell("address",VLOOKUP(A1,Sheet2!A1:E,2,FALSE)),"Sheet2!",""),
        "$",""),
      VLOOKUP(A1,Sheet2!A1:E,2,FALSE)
)

That’s all about hyperlinking to Vlookup output/result cell in Google Sheets. Enjoy!

Example Sheet

Resources:

22 COMMENTS

Baloney Sandwitch March 13, 2023 At 2:28 am

Very good. Also, you can use the MATCH function instead of the VLOOKUP to find the destination cell reference.

ameerah July 15, 2022 At 2:26 pm

Hey, I tried this. However, when I repeat the words, it does not work anymore.

Gareth March 23, 2022 At 9:25 am

This is great, thanks!

A little bit simpler if you use MATCH to get just the numeric part of the cell reference and then combine that with the column letter.

Then you don’t need to SUBSTITUTE anything.

- Prashanth March 25, 2022 At 7:19 am
  
  Hi, Gareth,
  
  That’s correct. But, anyhow, we have to use one Vlookup.
  
  So, I thought to use that formula again for getting the cell address. Then it would be easy for the explanation.
  
Darken October 17, 2020 At 3:23 am

Hi, great work!

I have the following problem with your solution:

I make a copy each year of my sheet (Sheet Blank) and rename it to the current year (Sheet 2019, Sheet 2020, Sheet 2021, etc…) to enter new data for the year. The hyperlinks still jump to the original blank sheet instead of the cell in the current sheet. Is there a way to get the link to the current sheet?

PS: I mean by formula, not having to change the URL manually in every hyperlink each year.

Thanks.

- Prashanth October 17, 2020 At 8:50 am
  
  Hi, Darken,
  
  As you may already know, every sheet has its own unique URLs. I don’t have the formula to extract the URL of a sheet.
  
Dale Harper June 26, 2020 At 3:32 pm

Hi – Your work really is excellent.

Can this above be used in conjunction with other formulas?

I used your tutorial to build what I needed, however, I would now like to add the hyperlink to source location function but am struggling to get it to work:

=CELL("address",ARRAYFORMULA(IFERROR(INDEX('2 - Alpha-Date Order'!I$2:I,SMALL(IF(B3='2 - Alpha-Date Order'!A3:A,ROW('2 - Alpha-Date Order'!A3:A)-1),$D$1)))))

- Prashanth June 27, 2020 At 10:14 am
  
  Hi, Dale Harper,
  
  Please remove the below formula parts from your above formula and the corresponding two closing brackets.
  
  ARRAYFORMULA(IFERROR(
  
  Please let me know whether it works or not. If not, I’ll publish a new tutorial ASAP to help you and other readers to address the issue.
  
  The ArrayFormula supports the non-array IF function. It’s not necessary to use when using the IF inside Index. The ArrayFormula would cause issue in the CELL function.
  
Donna June 22, 2020 At 3:14 pm

How to get the link if the list is on an external sheet?

Nereida May 1, 2020 At 7:07 pm

Hi Prashant,

First of all, many thanks for your contributions! You help me learn a lot 🙂

So, for your reference, I am a Google Sheets user from SPAIN (Windows & Firefox), and have noticed that where your formulas use “,” I have to use “;” to separate arguments. I am not sure if there are other differences I may not be aware of.

That said… I’ve tried this wonderful idea but it does not work 🙁

So maybe you can help?

If I pass this:

=CELL("address";VLOOKUP(B3;AUX!I2:M20;2;FALSE))

Then the output is #N/A [ “Argument must be a range”]

Checking the function help it says “address” output is the absolute reference of the first left cell from the RANGE provided as the second argument…

The output of VLOOKUP, or HLOOUP for that matter, is the content of the cell, of course, then, it is not a range.

Hopefully, you can help with this.

Thanks!

- Prashanth May 2, 2020 At 3:40 pm
  
  Hi, Nereida,
  
  I have tested and your formula returns a proper cell address. The Vlookup + Hyperlink formula for your region, as per your explanation (I’ve read between lines), might be this.
  
  =hyperlink( "use_cell_Aux!A1_link_as_instructed"& substitute( substitute( CELL("address";VLOOKUP(B3;Aux!I2:M20;2;FALSE));"Aux!";""); "$";""); VLOOKUP(B3;Aux!I2:M20;2;FALSE) )
  
  Please replace use_cell_Aux!A1_link_as_instructed in the formula.
  
  If this is not helpful, you can copy my example sheet (link already left within the post) and convert its Locale to yours from that sheet File > Spreadsheet settings > Locale > Spain.
  
  To know more about changes in the formula due to Locale, please read this guide – How to Change a Non-Regional Google Sheets Formula.
  
  - I November 5, 2020 At 10:55 am
    
    I too have this issue. I can replicate your example in the same sheet, different tabs, but when I go to apply this on a separate tab, I also get #N/A [ “Argument must be a range”] when I split out the "=CELL("...) formula for testing. When I apply the whole formula, it produces the right text result in the cell, but it does not apply the hyperlink. Strange…
    
    - Prashanth November 5, 2020 At 1:00 pm
      
      Can I have a look at your sheet?
      
      If it doesn’t contain sensitive or personal data, consider sharing the link with you reply below (it won’t be published).
      
    - Prashanth November 5, 2020 At 1:01 pm
      
      Can I have a look at your sheet?
      
      If it doesn’t contain sensitive or personal data, consider sharing the link with your reply below (it won’t be published).
      
Bohdan April 16, 2020 At 2:14 am

Hello!

I’ve been searching for that !!
I did as you wrote, but it doesn’t recognize it as the right formula.
Some mistakes were found.

My case is:
I have a Master Sheet where I put all my data.
There are a couple of sheets that query data from that Master Sheet.
I need to jump to any cell in the Master Sheet to edit it.

- Prashanth April 16, 2020 At 9:21 am
  
  Hi, Bohdan,
  
  To help you I have included a sample sheet. You can find that at the very end of the post.
  Cheers!
  
Manuel December 17, 2019 At 2:22 pm

Hi,

Thanks for sharing.

Unfortunately, when I reference a cell in another tab of the sheet, it goes to the other tab by clicking BUT not the referenced cell as it says that the link area has been deleted…

- Prashanth December 17, 2019 At 5:44 pm
  
  I think I can correct it. Can you replicate the problem in another file and share that with me?
  
David Moore November 26, 2019 At 4:04 am

This is great – I’m forever searching back through sheets to find the source!
Can this also be made to work in an arrayformula? (as all my vlookups do, better than index-match….)

- Prashanth November 26, 2019 At 9:43 am
  
  Hi, David,
  
  My formula won’t work with a Vlookup array. The reason is the CELL function which I have used to find the cell address of the Vlookup result cell.
  
  Best,
  
Ellen Guldan July 25, 2019 At 11:39 pm

OMG, I have been looking for this for weeks. You solved all my problems today!! I have to say I did not expect to find it paired with “Vlookup” in the title, so possibly I had overlooked it–I use index match myself (and by doing so, avoid the need for the cell function in the formula). THANK YOU
THANK YOU
THANK YOU!

- Prashanth July 26, 2019 At 8:58 am
  
  Hi, Ellen,
  
  Welcome!
  
  Thanks for the feedback!