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.
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.
Hit enter after entering the formula.
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.
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.
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.
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.
You could have also used VLOOKUP formula in the first example.
Hope this helped.