Hyperlinks and VLOOKUP Error

[jacksum336]

Hi,

I have the following formula which returns a set of hyperlinks from data it searches through.
=HYPERLINK((IFERROR(VLOOKUP(D5,A2:C91,3,FALSE),"""")))
Unfortunately, when I click on the links, it says "Cannot open the specified file."

Thanks!

 

[jasonb75]

Just a guess. Your lookup table only contains the file name, to open it with a hyperlink requires the full file path.

 

[jacksum336]

Hi Jason,

Thanks for your help.
Essentially (this is all on one sheet).

I have in Column C-Index
C1:C27 are hyperlinks to other worksheets within the same workbook.

The search engine filters through the data:
=HYPERLINK((IFERROR(VLOOKUP(D5,A2:C91,3,FALSE),"""")))

The search results are hyperlinks and when I click on them, it gives me the error ""Cannot open the specified file."
Any advice would be appreciated!
Thanks!

 

[jasonb75]

The formula still needs to a file path to function correctly, although it is simplified if it's within the same workbook.

Are you saying that if the match for D5 in the lookup formula is found in A20 then you want the hyperlink to jump to C20?

 

[jacksum336]

Hi Jason,

Yes, if the match for D5 is found in Columns (C2:C20), than I can click on D5 and it will jump to the right tab.
Columns C2:C20 are all hyperlinks right now. Thank you!

 

[jasonb75]

That is not how the functions work, but I think it is how many people think it will work.

Vlookup will read the text that is in the result cell, not the address of the cell, it is that text that is then passed to the hyperlink formula, not the cell address where the result is found.

The formula to return the correct underling address to the hyperlink is a bit more complex, see if this one does what you want it to.

=IFERROR(HYPERLINK(ADDRESS(MATCH(G5,$A$2:$A$91,0)+1,3,,,MID(CELL("filename",G5),FIND("[",CELL("filename",G5)),LEN(CELL("Filename",G5)))),VLOOKUP(G5,$A$2:$C$91,3,0)),"")

You might find that it doesn't work on a new workbook that has never been saved.

 

[jacksum336]

Hi Jason,

Thanks for your help.
Is filename the name of the workbook or tab?
Also, why is G5 the reference? Thanks.

 

[jasonb75]

I guess that can be more than a little misleading, just leave "filename" as it is, it's a command for the CELL function, telling it to get the filename for the specified cell.

I have no idea why I used G5 as a reference, it should be D5 as with your earlier formula.