Hyperlinks and VLOOKUP Error

jacksum336

New Member
Joined
Jun 16, 2015
Messages
10
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!
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,761
Office Version
  1. 365
Platform
  1. Windows
Just a guess. Your lookup table only contains the file name, to open it with a hyperlink requires the full file path.
 

jacksum336

New Member
Joined
Jun 16, 2015
Messages
10
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

Well-known Member
Joined
Dec 30, 2008
Messages
14,761
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 16, 2015
Messages
10

ADVERTISEMENT

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

Well-known Member
Joined
Dec 30, 2008
Messages
14,761
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

jacksum336

New Member
Joined
Jun 16, 2015
Messages
10
Hi Jason,

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

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,761
Office Version
  1. 365
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,984
Messages
5,834,710
Members
430,311
Latest member
JAC0617

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top