Excel Hyperlink to Cell in Another Sheet with VLOOKUP (With Easy Steps)

This article illustrates how to create an Excel hyperlink to a cell in another sheet along with the VLOOKUP formula. The hyperlink feature in excel allows us to create and go to active links. You can search for data from different worksheets and go to that sheet by combining the hyperlink feature with the VLOOKUP formula. The following article highlights the purpose of this article. Have a quick look through the article to learn how to do that.

An Overview of Excel HYPERLINK Function

Function Objective:

The HYPERLINK function in Excel creates a shortcut or jump or link that opens a document on a hard drive, a network server, or on the Internet. It contains the following arguments.

Syntax:

HYPERLINK(link_location, [friendly_name])

Arguments:

link_location : Required. Asks for the link address or its location.
friendly_name : Optional. Displays this name for the created link.

An Overview of Excel VLOOKUP Function

Function Objective:

The VLOOKUP function in Excel looks for a value in the leftmost column of a table and then returns a value in the same row from a specified column. It contains the following arguments.

Syntax:

VLOOKUP(lookup_value, tabble_array, col_index_num, [range_lookup])

Arguments:

lookp_value : Required. Search for this value within a range.
tabble_array : Required. The range to search for the lookup_value.
col_index_num : Required. The column number in the table_array to return a value with respect to the lookup_value.
[range_lookup] : Optional. It is 0 or FALSE by default meaning Exact Match. 1 or TRUE means Approximate Match.

How to Create Hyperlink to a Cell in Another Sheet with the Excel VLOOKUP Function

Imagine you have the following dataset in Sheet2 and Sheet3. Sheet2 contains the list of top companies in the USA. On the other hand, Sheet3 contains the list of top smartphone brands there. You will find it annoying to toggle between the sheets to get to the desired list if you have many sheets like this in the workbook. We will show you how you can search for a keyword and directly go to a cell in a different worksheet containing the keyword by clicking an auto-generated hyperlink.

Steps to Create Hyperlink to a Cell in Another Sheet with VLOOKUP Function

📌 Step-1: Create a Helper Sheet

First, you need to create a helper sheet containing the keywords and their location. Here, Sheet4 fulfills this purpose. Notice carefully that the hash (#) sign can replace the name of This Workbook.

Now suppose you want to search for the keywords and generate the hyperlinks in Sheet1. You can save some time by creating a dropdown list using the keywords. This way you won’t have to type something every time you want to look up a value. Select cell B3 to be able to do that.

📌 Step-2: Apply Data Validation

Then press ALT+D+L+L or select Data >> Data Validation to open the Data Validation window. Next, choose List as the Validation criteria from the Settings tab.
After that, enter the following formula in the Source Then hit the OK button.

=Sheet4!$B$5:$B$6

Now you will see the following dropdown list. Choose a keyword from the list using the dropdown arrow at the lower-right corner of the cell.

📌 Step-3: Enter the HYPERLINK-VLOOKUP Formula

Then, enter the following formula in cell C3. This will create a clickable link as shown below. The VLOOKUP function in the formula returns the link_location argument for the HYPERLINK function.

=HYPERLINK(VLOOKUP(B3,Sheet4!$B$5:$C$6,2,FALSE),"Click Here")

You can remove the friendly_name argument from the formula. Then you will be able to see the actual address of the link.

Now you can hide the helper sheet if you want.

If VLOOKUP Hyperlink Cannot Open Specified File

Suppose the addresses for the keywords only contain the sheet names and cell references as follows.

Then excel will show the “Cannot open the specified file.” error as shown in the following picture.

You can add a hash (#) sign or the workbook name inside third brackets before the addresses. Alternatively, you can use the modified formula given below.

=HYPERLINK("#" & VLOOKUP(B3,Sheet4!$B$5:$C$6,2,FALSE),"Click Here")

Read More: Hyperlink in Excel Not Working

Create Hyperlink to a Sheet in Different Excel Workbook

So far we have seen how to create a hyperlink to a cell in a sheet in the same workbook. Now, we will do this for a sheet in a separate workbook.

Assume you have a different source workbook in the following path.

It contains the following dataset.

Now you can create a hyperlink to directly go to the source workbook from your active workbook. Just enter the following formula in cell B4 to do that.

=HYPERLINK("[D:\ExcelDemy\Source.xlsx]Sheet1!$B$2","Open Source Workbook")

Clicking on the hyperlink will take you to the source workbook. But you need to manually change the file path whenever you change the location of the source file.

Alternatively, you can press CTRL+K while in cell B4 to avoid this annoying task by using the Insert Hyperlink window.

Things to Remember

You need to change the file paths whenever the location addresses are changed.
You can use the Edit Links feature from the Data tab to modify the links.

Download Practice Workbook

You can download the practice workbook from the download button below.

Hyperlink to Cell in Another Sheet.xlsx

Conclusion

Now you know how to create an Excel hyperlink along with the VLOOKUP formula. Please let us know if this article has helped you to solve your problem. You can also use the comment section below for further queries or suggestions. Stay with us and keep learning.

<< Go Back To Excel Hyperlink to Another Sheet | Hyperlink in Excel | Linking in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!