Do you want to learn a super cool link building technique in Google Sheets? Well, you’re in luck! In this tutorial, I’ll show you how to search for a value and hyperlink the cell where it’s found. Trust me, it’s easier than you might think!
How to Search Value and Hyperlink Cell Found
To make things easier, let’s start by importing a table into Google Sheets. You can follow along by using this formula in your Sheet1!A1:
=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_states_and_territories_of_the_United_States","table",1)
This formula will populate a table with the names of states and territories of the United States. Pretty neat, huh? But for this example, we only need a one-column list. So, let’s modify the formula a bit:
=Index(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_states_and_territories_of_the_United_States","table",1),0,1)
Now that we have our list, let’s move on to Sheet2! In cell A1, I have the name “Hawaii”. Our goal is to link this text to the matching value in cell A13 in Sheet1. Exciting, right?
Steps to Hyperlink a Value to a Cell in a List in Google Sheets
Here are the simple steps to achieve our goal:
-
In Sheet1!B1, enter this ArrayFormula:
=ArrayFormula(if(len(A1:A),"URL"&row(A1:A),)) -
Right-click on cell A1 in Sheet1 and select “Get link to this cell”. Replace the text “URL” in the formula with the URL you just copied.
-
Remove the column identifier from the URL. In the formula, it should look something like this:
=ArrayFormula(if(len(A1:A),"https://docs.google.com/spreadsheets/d/1NpigIKudZnn9zPQPJoe-w7_86HHpakHkOb6WyRCv3MI/edit#gid=0&range=A"&row(A1:A),)) -
The above formula generates an array of URLs connected to the values in column A.
-
Head over to Sheet2!B1 and use this formula to search for the value “Hawaii” in Sheet1, column 1, and return the URL from column 2:
=vlookup(A1,Sheet1!A1:B,2,0) -
But wait, we want a hyperlink instead! Don’t worry, we got you covered. Use this syntax to create a hyperlink:
HYPERLINK(URL, [link_label]) -
Replace the URL with the above vlookup based formula and the link_label with A1.
Voila! The final formula in cell B1 in Sheet2 will be:
=hyperlink(vlookup(A1,Sheet1!A1:B,2,0),A1)
And there you have it! With this formula, you can easily search for a value and hyperlink the cell where it’s found in Google Sheets. But wait, what if you want to search for multiple values and bulk hyperlink them? Fear not, my friend. The formula for that would be:
=ArrayFormula(IFERROR(hyperlink(vlookup(A1:A,Sheet1!A1:B,2,0),A1:A)))
Conclusion
I know, searching for values and hyperlinking the cells where they’re found might not be the most common task. But let me tell you, many Excel users out there find this technique incredibly useful. The approach is slightly different in Excel and Sheets, but once you get the hang of it, you’ll be a pro!
So go ahead, give it a try. Impress your friends with your newfound Google Sheets skills. And remember, if you need more tips and tricks like this, be sure to check out Crawlan.com. Happy sheeting!
