Non working Hyperlinks when results returned from another sheet

[dave6726]

I have the following problem.
Sheet 2 has a list of names and hyperlinks to other sheets (hyperlinks work)
On sheet one I have created an” Index - Small” formula that searches sheet2 and brings back the results on sheet1
=IF(ISERROR(INDEX(Sheet2!$A$2:$B$497,SMALL(IF(Sheet2!$A$2:$A$497=Sheet2!$A$2,ROW(Sheet2!$A$2:$A$497)),ROW(2:2))-1,2)),"",INDEX(Sheet2!$A$2:$B$497,SMALL(IF(Sheet2!$A$2:$A$497=$G$9,ROW(Sheet2!$A$2:$A$497)),ROW(2:2))-1,2))

However, I also want to bring the hyperlinks results from sheet2. I have used the following.
=HYPERLINK(VLOOKUP(G12, Sheet2!$D$2:$E$10,2,0)) - (name in col D and hyperlink in col E)

My problem is that although the results show as a hyperlink on sheet1, the hyperlinks do not work from sheet1 and I am getting an error message saying “Cannot open the specified file”.

As I said the hyperlink works on sheet2 but not from sheet1 when I pull the results back.
Any advice would be greatly appreciated.

 

[FormR]

I was wondering if there is a way of tidying it up? i.e. Just to show FRED, but for the hyperlink to still work.

Hi, if you look back to the original suggestion in post # 10 you will see that the VLOOKUP() function can be repeated in the "Friendly_Name" parameter.

i.e.

=HYPERLINK("#'"&VLOOKUP(G12,Data!D2:E10,2,0)&"'!A1",VLOOKUP(G12,Data!D2:E10,2,0))

Note that it does add a little extra overhead for obvious reasons.