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

Leave a Reply