Hyperlink to Cell of VLOOKUP Resultes

[buckyswider]

(sorry, can't figure out addins in office 2016 right now, so I can't properly post the snippets. But that's not my main issue right now!)

Hi all, been searching for about an hour now, and I'm getting very close to the solution, but no guitar.

I have a vlookup function that works properly. Evaluates and displays data from a different sheet ("Coats"_ in the same workbook based on the value in column A:

=VLOOKUP(A2,Coats!A:C,2,FALSE)

So it takes a name from column A, matches it in column A of the "Coats" sheet, and then displays the value from row b- in this case the serial number (it's a spreadsheet for firefighter gear, in case you're wondering what kinds of coats have their own serial numbers!)

What I want to do is to be able to click on that value and be taken to the location in the "Coats" sheet, as that has much more data than the "cover" sheet. Simply wrap it in HYPERLINK, right?

=HYPERLINK(VLOOKUP(A2,Coats!A:C,2,FALSE))

I do that and the value LOOKS like a hyperlink. But when I click on it I get a "Cannot open the specified file" error box. So how do I code the vlookup/hyperlink to just evaluate to the current file/path?


Thanks!

 

[mrshl9898]

Is this it?

=HYPERLINK("#"&"Coats!"&ADDRESS(MATCH(A2,Coats!A:A,0),2),VLOOKUP(A2,Coats!A:C,2,FALSE))

 

[buckyswider]

Is this it?

=HYPERLINK("#"&"Coats!"&ADDRESS(MATCH(A2,Coats!A:A,0),2),VLOOKUP(A2,Coats!A:C,2,FALSE))

Aha, brilliant, thank you! I guess I was evaluating the cell value and trying to jump to a location that would be the serial number- not the location OF that serial number on the "coats" sheet!

Gonna take me a while to digest the intricacies of that syntax, but it works!

Thanks very much!!

 

[mrshl9898]

Not a worry, the # threw me off, just remember you need it before the sheet name and cell reference

 

[NNOG28]

I would like to know if I want to do the hyperlink with Vlookup to a different excel file. please help.

 

[buckyswider]

I would like to know if I want to do the hyperlink with Vlookup to a different excel file. please help.

use brackets around the path/filename and single quotes around that along with the sheet name.

so VLOOKUP(A1,'[Myfilename.xls]mySheet'!$A$1:$B$99,2,0)