Hyperlink to Index-Match Output in Google Sheets

Google Sheets supports creating a hyperlink to an Index-Match output cell. The output thus returned (Index-Match value) will be a clickable link (hyperlink) in Google Sheets.

Please do note one thing. The row/column argument in Index should not be 0 as it causes multiple values in the output.

In short, as per my tutorial, you can only hyperlink to a single cell output of Index-Match in Google Sheets.

Introduction to Hyperlink Index and Match in Google Sheets

Index-Match is a popular alternative among Excel users for Reverse Vlookup. But it now seems replaced by a new function called XLOOKUP.

As far as I know, there is no XLOOKUP function so far in Google Sheets. I guess Google will include this function soon (I may be wrong)!

The below picture says why I think so.

XLOOKUP Function in Google Sheets -Not Available?

In Google Sheets, for reverse Vlookup, it doesn’t require the XLOOKUP as Vlookup itself is capable of that.

You May Like: Reverse Vlookup Examples in Google Sheets [Formula Options].

Even though it’s not a must to use Index-Match, many Google Sheets users, who are well conversed in Excel, prefer to use Index-Match.

So in this post let’s learn how to hyperlink Index-Match result in Google Sheets. It makes sense, right?

Here I’m following the same approach that I have used in hyperlinking to Vlookup output cell in Google Sheets. Just I’m replacing Vlookup in that approach with Index-Match here.

How to Hyperlink to Index-Match Result in Google Sheets

I am using a very simple table so that you can easily create it for the testing of my formula. It contains the names of a few countries and their national capitals.

National CapitalCountry Names
Abu DhabiUnited Arab Emirates
BerlinGermany
CairoEgypt
CanberraAustralia

Create the above table in the range A1:B5 and rename the sheet as ‘data’. Then follow the below steps to create a hyperlink to Index-Match output cell in Google Sheets.

Index-Match Formula (for Hyperlinking)

I know you are already using Index-Match. Still, I’m explaining the use to make this tutorial beginner-friendly.

Example:

I want to match “Germany” in the above table in column B, then offset that much number of rows in column A and get the value “Berlin”.

To match the country (string) “Germany” in column B we can use the MATCH function by following this syntax – MATCH(search_key, range, search_type).

Key the search_key “Germany” in cell D1 and use this Match formula in cell E1. The formula would return 3.

=match(D1,B1:B,0)

That means we want to offset 3 rows in another column, here column A and fetch the value “Berlin”. How?

We can use the INDEX function as per the syntax – INDEX(reference, row, column).

=INDEX(A1:A, 3, 1)

Simply replace the # 3 with the Match formula itself. Here is that Index-Match formula to return the “Capital” of the “Country” Germany.

=index(Data!A1:A,match(Data!D1,Data!B1:B,0),1)
The Index-Match Formula to Hyperlink

Note:

The # 1 in the formula represents the column number to offset. The Index ‘reference’ is A1:A, which means column 1.

If the ‘reference’ is A1:E and you put 5 as column number, then the formula would offset 3 rows in column E.

Now let’s see how to hyperlink to this index-match output/result in Google Sheets or we can say make the label “Berlin” in cell E1 clickable.

Hyperlinking Techniques

There are actually 3 steps involved in hyperlinking an Index-Match result in Google Sheets. We have already completed step 1 in that! Here are those 3 steps.

  1. Index-Match formula (we will hyperlink the output of this formula): ✓
  2. URL of the cell A1 from the ‘data’ sheet. Here my sample table is in the sheet named ‘data’: ✘
  3. Cell ID of the Index-Match formula output: ✘

Only the steps 2 and 3 are pending.

Step 1

=index(Data!A1:A,match(Data!D1,Data!B1:B,0),1)

Step 2

To get the URL, right-click on cell A1 in the ‘data’ sheet and click “Get link to this cell”.

Right-click and paste the copied link in cell E2. After pasting, edit the link to remove the cell reference A1 at the end of the link.

https://docs.google.com/spreadsheets/d/****#gid=92***9&range=

The above URL is a sample. You must use the link copied from your sheet as above. The = sign must be the last character after editing the link as mentioned above.

Step 3

In cell E3, use the step 1 Index-Match formula within the CELL function to get the cell ID of the Index-Match output.

Please follow the below Generic Formula for getting the cell ID of the Index-match result value.

=cell("address",index-match_formula)

Formula:

=cell("address",index(Data!A1:A,match(Data!D1,Data!B1:B,0),1))

The above formula would return the cell address $A$3. We must clean it by removing the $ sign from the formula.

Also if your table is ‘data’ sheet and the formula is in another sheet in that file, then the result would be data!$A$3.

So whether the sheet name is there or not with the cell address, let’s include the provision to remove the string data! from the result.

Just modify the formula in cell E3 to include multiple Substitute functions as below.

=substitute(
           substitute(
           cell("address",index(A1:A,match(D1,B1:B,0),1)),"data!",""),
        "$","")

The first Substitute removes the $ sign whereas the second one removes the data! sub-string if any. The result of the above formula will be the cell ID ‘A3’.

Using Step 1, 2, and 3 Formulas within Hyperlink to Create Hyperlink to Index-Match Result

The Hyperlink function takes two arguments. They are ‘URL’ and ‘link_label’.

Syntax: HYPERLINK(URL, [link_label])

We can use the Index-Match formula (step 1) as the ‘link_label’ in Hyperlink. What about ‘URL’?

The ‘URL’ must be the combined output of step 2 and step 3 formulas. The following Generic formula will help you understand this.

=hyperlink(
     "step_2"&
        step_3,
      step_1
)

Formula that Hyperlinks Index-Match Output (based on the Generic Formula above):

=hyperlink(
     "https://docs.google.com/spreadsheets/d/****#gid=92***9&range="&
        substitute(
           substitute(
           cell("address",index(A1:A,match(D1,B1:B,0),1)),"Data!",""),
        "$",""),
      index(data!A1:A,match(data!D1,data!B1:B,0),1)
)

Now remove formulas in the cell E1, E2, and E3. Insert our above final formula in cell E1 and voila!

Hyperlink Index-Match in Google Sheets

Hyperlink Example 17520

Resources:

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

Finding Most Frequent Text in Excel with Dynamic Array Formulas

Looking to identify the most frequently occurring text in Excel? You can do this...

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

4 COMMENTS

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.