Have you ever wanted to create a hyperlink to a Vlookup output cell in Google Sheets? Well, you’re in luck! Vlookup is an incredibly versatile tool in Google Spreadsheets, and today I’m going to share a “crazy” Vlookup formula that will allow you to create a clickable link to the relevant cell in your lookup table.
Imagine this scenario: you’re working on a spreadsheet with two sheets, let’s call them “Sheet1” and “Sheet2”. From “Sheet1”, you want to search for a key in a table on “Sheet2” and return a value from the row where the key is found. But here’s the exciting part – with this formula, you can click on the Vlookup output in “Sheet1” and instantly jump to the cell in “Sheet2” where that value resides. Pretty neat, right?
Now, this technique works not only between separate sheets but within a single sheet as well. Let’s dive into the steps involved in creating a hyperlink to a Vlookup output cell in Google Sheets.
Steps to Create Hyperlink to Vlookup Output Cell in Google Sheets
Sample Data in ‘Sheet2’
Before we begin, let’s take a look at the sample data in “Sheet2”. (Insert the image from the original article here)
In “Sheet1”, my search key is located in cell A1, and it’s “II-2053”. In cell B1, we can use the Vlookup formula to search for this key in “Sheet2!A1:E” and return the value from the second column (column B) in the found cell. But we want to take it a step further and create a hyperlink to this Vlookup result cell. Let’s see how.
Step # 1: Vlookup Formula Without Hyperlinking
First, let’s establish the Vlookup formula without the hyperlink. In cell B2 of “Sheet1”, enter the following formula:
=VLOOKUP(A1,Sheet2!A1:E,2,FALSE) If you’re familiar with Vlookup in Google Sheets, you’ll know that this formula will return the value “Gravel 40-60 mm” since we provided the column number 2.
Step # 2: Find the Cell Address of Vlookup Result in Google Sheets
Now, let’s find the cell address of the Vlookup result. To do this, we need to enclose the Vlookup formula with the Cell function. Enter the following formula in any blank cell:
=CELL("address",VLOOKUP(A1,Sheet2!A1:E,2,FALSE))This will return the cell address, in this case, “Sheet2!$B$5”. But we only want the cell reference, “B5”, without the sheet name and dollar sign.
Step # 3: Removing Sheet Name and $ Sign from a Cell Address in Sheets
To remove the sheet name and dollar sign from the cell address, we can use a nested Substitute formula. Enter the following formula in any blank cell:
=SUBSTITUTE(SUBSTITUTE(CELL("address",VLOOKUP(A1,Sheet2!A1:E,2,FALSE)),"Sheet2!",""),"$","")The result will be the desired cell reference, “B5”.
Step # 4: Creating a Dynamic Hyperlink URL in Google Sheets
In this step, we’ll create a dynamic URL in Google Sheets to achieve our goal of creating a hyperlink to the Vlookup output cell. Here’s how:
- Go to “Sheet2” and right-click on the cell that contains the URL, let’s say ‘Sheet2!A1’. Select “Get link to this cell”.
- Paste the copied link into any blank cell. You will get a URL like this: “https://docs.google.com/spreadsheets/d/1-x9eKz-OF343Pgj44X…41048&range=A1“.
- Replace the “A1” at the end of the URL with the nested Substitute, Cell, and Vlookup combination formula we obtained earlier. It should look like this:
"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
Now we can finally create the hyperlink formula.
We’ll use the HYPERLINK function in Google Sheets to accomplish this. Enter the following formula in any blank cell:
=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))Replace the URL with the formula we created in Step 4, and the link_label with the Vlookup formula from Step 1.
And voila! You’ve just created a hyperlink to the Vlookup output cell in Google Sheets. Now you can easily navigate between sheets and find the information you need quickly.
If you want to see the entire process in action, check out our example sheet [(insert hyperlink labeled ‘Example Sheet’ here)]. It will guide you step by step through the process.
Happy hyper-linking! 🙂
Resources:
