return an address from VLOOKUP and hyperlink to cell with original value

[deckerp]

return an address from VLOOKUP and hyperlink to cell with original value

A1 has a VLOOKUP that returns a value. I would like A1 to be linked via HYPERLINK (requires an address) to the cell where the value comes from.

the formula I have is:
=HYPERLINK(VLOOKUP($C6;Activities!$A:$AA;MATCH(D$5;Activities!$6:$6;0);FALSE);LEFT(VLOOKUP($C6;Activities!$A:$AA;MATCH(D$5;Activities!$6:$6;0);FALSE);40))

but it is not working and gives the error: Cannot open specified file.

 

[jbeaucaire]

I'm not following your formula, but here's one I've used to do the same thing, slightly simpler, perhaps you can adapt it:

=HYPERLINK("#"&ADDRESS(MATCH(A1,B1:B5,0), 2), A1)