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.

Excel Hyperlink to Cell in Another Sheet with VLOOKUP


Download Practice Workbook

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


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")

Excel Hyperlink to Cell in Another Sheet with VLOOKUP

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

Excel Hyperlink to Cell in Another Sheet with VLOOKUP

  • Now you can hide the helper sheet if you want.

Read More: Excel Hyperlink to Another Sheet Based on Cell Value


Similar Readings


If VLOOKUP Hyperlink Cannot Open Specified File

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

  • 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")

How to Fix If VLOOKUP Hyperlink Cannot Open Specified File


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.

How to Create Excel Hyperlink to a Sheet in Different Workbook

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

How to Create Excel Hyperlink to a Sheet in Different Workbook

Read More: [Fixed!] โ€˜This workbook contains links to other data sourcesโ€™ Error in Excel


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.

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. Do visit our ExcelDemy blog to read more about excel. Stay with us and keep learning.


Related Articles

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo