Build Hyperlink With VLOOKUP in Excel

You might have come across a task in which you were assigned to build hyperlinks, which seems very easy, and if you are new to excel or don’t have enough experience with it, then you might wonder about doing this task manually but let me add that no doubt it seems easy to build two or three hyperlinks manually, but when it comes to building a bulk of hyperlinks, then it becomes a cumbersome task to do it manually moreover you will end up exhausted, and you might not finish the task on time.

But don’t worry about it because we got your back and brought up the easiest way, which is none other than with the aid of the Vlookup Function, which lets you build bulks of hyperlinks within seconds.

4 Build hyperlink with VLOOKUP1

So without any delay, let’s dive into it

 General Formula 

To create hyperlinks using VLOOKUP, use the formula below.

=HYPERLINK(VLOOKUP(Lookup_name,Lookup_range,column_number,0), Lookup_name) 

Explanation Of Syntax

  • Hyperlink: The HYPERLINK function creates a clickable hyperlink. This Function can develop connections to workbook locations, internet sites, or files on network servers.
  • VLOOKUP: This Function helps lookup data in a range or table row by row. More information on the VLOOKUP function may be found here.
  • The comma symbol (,): This symbol is a separator that aids in the separation of a list of values.
  • Parenthesis (): This symbol’s Primary Function is to group the elements.

Summary

You may use the VLOOKUP function in conjunction with the HYPERLINK function to produce a hyperlink from a lookup.

The formula in F5 in the case provided is:

=HYPERLINK(VLOOKUP(E3,link_range,2,0),E3)

4 Build hyperlink with VLOOKUP1

Explanation

The hyperlink function allows you to use a formula to build a functioning link. It accepts two parameters: link location and, optionally, familiar name.

VLOOKUP searches for and obtains a link value from column 2 of the designated range “link_range” from the inside out (A3:B5). The lookup value is taken from column E, and VLOOKUP is set to an exact match.

The result is used as link location in HYPERLINK, while column E’s content is used as a friendly name.

HYPERLINK function returns a functional link.

As an example,

To create hyperlinks in Excel using VLOOKUP, follow the instructions below.

  • To begin, you must prepare sample data in Excel.

4 Build hyperlink with VLOOKUP1

  • Then, insert the following formula in the formula bar to create a hyperlink from a lookup. Use the Formula
=HYPERLINK(VLOOKUP(E3,link_range,2,0),E3)
  • To obtain the result, press the Enter key, as illustrated in cell F3.

4 Build hyperlink with VLOOKUP1

  • Next, insert the following formula in the formula bar to create a hyperlink from a lookup. Use the Formula
=HYPERLINK(VLOOKUP(E4,link_range,2,0),E4)
  • Finally, press the Enter key to obtain the result, as shown in cell F4 below.

4 Build hyperlink with VLOOKUP1

Cell F4, as a result

In another example, we want to retrieve the Link URL from column C based on the Link Name in E3 (Google). We want to build a hyperlink with a name based on those two variables. The outcome is shown in cell F3.

The equation is as follows:

=HYPERLINK(VLOOKUP(E3, $B$3:$C$6, 2), E3)

Cell E3 contains the lookup value. The table array argument has the value range $B$3:$C$6. Because the range does not change when the formula is duplicated, it must be fixed. Col index num is set to 2, indicating that we want to get data from the second column in the range. Finally, the default value for range lookup is 0 since we want to discover an exact match of “Lookup column” values.

The link location parameter for the HYPERLINK function is the result of the VLOOKUP function. Cell E3 is the friendly name parameter.

To use the HYPERLINK and VLOOKUP capabilities, we must first do the following steps:

Step1: Click on cell F3 to insert the formula:

=HYPERLINK(VLOOKUP(E3, $B$3:$C$6, 2), E3)

Step2:  Enter the formula

Step3:  Drag the formula down to the remaining cells in the column by clicking and dragging the small “+” button at the bottom-right of the cell.

4 Build hyperlink with VLOOKUP5

Use of the self-contained VLOOKUP formula

Consequently, the hyperlink for https://www.google.com with the name “Google” will appear in cell F3. The VLOOKUP table shows that this is the Link URL for the term Google from E3.

Conclusion

This article will teach you to efficiently create hyperlinks using VLOOKUP in MS Excel with examples and pictures.

Related Functions


  • Excel HYPERLINK function
    The Excel HYPERLINK function creates a shortcut/hyperlink to a document, when you click this hyperlink, the excel will open the file that is stored on a network server or local location.The syntax of the HYPERLINK function is as below:= HYPERLINK(link_location,[friendly_name])…
  • Excel VLOOKUP function
    The Excel VLOOKUP function lookup a value in the first column of the table and return the value in the same row based on index_num position.The syntax of the VLOOKUP function is as below:= VLOOKUP (lookup_value, table_array, column_index_num,[range_lookup])….
Related Posts

Calculate Total Cost with Excel VLOOKUP Function

In today's article we will show you how to calculate the total cost for a given weight using the Excel VLOOKUP function. This function will help us to find the appropriate unit price for that weight and then we can ...

Basic Price Discount Calculation with Excel VLOOKUP Function

We often encounter product discounts in our shopping. Depending on the level of spending, the mall will offer different percentages of discounts. Usually, the more you spend, the bigger the discount, while the less you spend, the smaller the discount. ...

Calculate Grades With VLookup in Excel

Why Should You Calculate Grades With VLookup Excel? If you're looking for a simple way to find out a student's grades, VLOOKUP Excel can do the trick. This function uses a lookup table to find the values and sort them ...

Convert State Names To Abbreviations
abbr state names1

Assume that you got a task to convert the full state's name into the abbreviations in MS Excel, and for doing this task, you might choose to do it manually, which is an acceptable way only if you don't have ...

Find and Replace Multiple Values
find replace multiple values5

This post will guide you how to find and replace multiple values at once with VBA macro or using formula  in Excel. How do I make multiple find and replace in Excel. Suppose that you have a few cells containing ...

Excel XLOOKUP Function

Excel XLOOKUP Function was added into Excel as a beta feature in August 2019 and is now accessible exclusively in Microsoft 365. (as of July 2021). However, if you fall into this category and often deal with big sets of ...

VLOOKUP Formula | Faster Trick with 2 VLOOKUPS

This post will guide you how to use 2 VLOOKUPS function to looking up data entries from a given range of cells in Microsoft Excel. VLOOKUP with 2 lookups can be faster than a single VLOOKUP in certain scenarios. The ...

VBA Macro For VLOOKUP From Another Sheet
vba macro for vlookup from another sheet1

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you how to use VBA code to vlookup data ...

VLOOKUP From Another Sheet Not Working
vlookup from another sheet not working3

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you reasons why your VLOOKUP formula is not working ...

Fix #N/A Error For VLOOKUP From Another Sheet
vlookup from anther sheet not working1

This post will show you how to fix the #N/A error why it occurs when you extract values from another sheet using VLOOKUP function in Excel 2016,2013,2010 or other Excel versions. How can you correct a #N/A error in VLOOKUP ...

Sidebar