Hyperlink In Excel Based On Cell Value

In this post, we will see how we could conditionally show hyperlink in a cell based on value of a different cell. As shown in the pic. below, we want to show hyperlink in cell B2 based on the value that we have in cell A2.

In cell E2 and E3, we have two hyperlinks that we will be shown conditionally based on the value in cell A2.

table showing how to show hyperlink dynamically in excel

In this example, we want to show the hyperlink 1 in cell B2, if the value in cell A2 is “Show Hyperlink 1” and show hyperlink 2 if the value in cell A2 is “Show Hyperlink 2”.

We could conditionally show hyperlink in excel based on the value of a cell by using IF formula in excel.

Insert the formula =IF(A2=”Show Hyperlink 1″,E2,E3), which basically tells excel to show hyperlink 1 if the value in cell A2 is “Show Hyperlink 1” or show hyperlink 2 otherwise.

formula to show hyperlink conditionally in excel

Hit enter after entering the formula.

hyperlink is shown dynamically in excel

You could see that the hyperlink is shown conditionally and if we change the content in cell A2 to “Show Hyperlink 2”, the cell B2 will show us hyperlink 2.

formula changing the hyperlink based on cell value in excel

Suppose we have more than two links to be shown based on the cell content of A2.

In this case, we will try to see how we could show hyperlinks based on the content of a different cell.

We will use VLOOKUP function in excel to get the relevant hyperlink, first we will have to create a table as shown below, which would tell us what hyperlink to be shown based on different  values.

Table in range D1:E5 lists down all the hyperlink values.

showing hyperlink conditionally based on cell value

Now insert formula =VLOOKUP(A2,$D$2:$E$5,2,FALSE)  in cell B2, so this formula is basically checking the value in cell A2, and pulling the corresponding link form our table.

formula to dynamically change the hyperlink in excel

One more thing to see for here in , you might not get hyperlink format in the cell B2, so to make the value in cell B2 a hyperlink, right click and change the format to hyperlink as shown in the figure below.

changing the format of hyperlink

You could have also used VLOOKUP formula in the first example.

Hope this helped.

Share The Knowledge

Random Posts

  • Color Comment Box In Excel

    If  you are a sport to have a different and not the typical color for the comment box in you […]

    Share The Knowledge
  • VBA To Open Latest Excel Workbook

    vba to open latest excel workbook in a folder, how to open most recent file from a folder in excel vba, open latest file from folder in excel vba, open most recent workbook in excel, code to open the most recent file from a folder in vba

    Share The Knowledge
  • Change Name Range In Excel

    Name range is of the most useful features in excel.The challenge in our day to day excel activities are that […]

    Share The Knowledge
  • Extract Numbers After A Character In Excel

    Let’s see how we could easily extract all the digits after a character in excel. There are several occasions in […]

    Share The Knowledge

Leave a Reply