Hyperlink Not Working After Vlookup or Index/Match Formula

[wallmaxmer]

I have used the following formulas in my worksheet and both bring back the correct text and a hyperlink, but the hyperlink does not work.

=HYPERLINK(INDEX('CM Data'!B3:E153,MATCH(C18,'CM Data'!B3:B153,0),4))
=HYPERLINK(VLOOKUP($C16,Generate!$B$2:$G$100,4,),'CM Data'!E9)

I've tested the hyperlinks on all the cells in the "CM DATA" sheet and they all work correctly. When I click on the hyperlink in the "WEEK 1" sheet, where I need the hyperlink to be pulled into, it looks like it completely changes the link and says the following, "The webpage at

https://xxxxxxxxxxxxxxxxxxxxx

might be temporarily down or it may have moved permanently to a new web address". The address it pulls up is entirely different than the hyperlinks in the "CM DATA" sheet. For example, I changed one just to be as simple as, "www.google.com" and then I run the formula and it brings back the error with the long HTTPS address I just mentioned. It actually looks like its my SharePoint address on my computer that it changes it to.

Can someone point me in the right direction? Thank you!

 

[chris smith5]

Hi wallmaxmer,

If you're experiencing issues with hyperlinks not working after applying a VLOOKUP or INDEX/MATCH formula, it's most likely because the formula has altered the original cell reference of the hyperlink.

You can try using the HYPERLINK function in Excel to create a hyperlink using the original cell reference. Here's an example of how to use the HYPERLINK function:

1. In a new cell, enter the HYPERLINK function using the original cell reference as the first argument and the hyperlink text as the second argument. For example: =HYPERLINK(A1,"Link Text")
2. Replace A1 with the cell reference that contains the formula, and "Link Text" with the text that you want to display for the hyperlink.
3. Copy the formula down to apply it to all the cells that contain the formula.

By using the HYPERLINK function, you should be able to create working hyperlinks that are not affected by the VLOOKUP or INDEX/MATCH formula.


Best Regards.