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!
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,886
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
11,886
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
11,886
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
11,886
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,916
Members
413,952
Latest member
JGer

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