hyperlink+vlookup

**eran3185** · 02-17-2009, 09:23 AM

hi

in cell b2 i have a formula that contain hyperlink+vlookup.
when i want to get this link ("push" cell b2) , i get this message "cannot open the specified file".
what can i do ?

**NBVC** · 02-17-2009, 09:44 AM

Try:

=HYPERLINK("#"&CELL("Address",INDEX($A$4:$B$7,MATCH(A2,A4:A7,0),2)),VLOOKUP(A2,$A$4:$B$7,2,0))

**eran3185** · 02-17-2009, 09:49 AM

thank's , but there is no other way ???

**NBVC** · 02-17-2009, 09:55 AM

The formula has to return a cell address in order for it to get the link...

what's wrong with the formula provided... the first part finds the address (using a variant of the Vlookup() formula) and the second part, returns the actual Vlookup() result...

**DonkeyOte** · 02-17-2009, 10:03 AM

Similar to NBVC but without use of CELL you could use:

Please Login or Register  to view this content.

**NBVC** · 02-17-2009, 10:09 AM

Originally Posted by DonkeyOte

Similar to NBVC but without use of CELL you could use:

Please Login or Register  to view this content.

Yeah, I was trying to figure out a way to get the Index function to return the cell address without the CELL() function, but it just wasn't working...

I had tried: =HYPERLINK("#"&INDEX(B4:B7,MATCH(A2,A4:A7,0),1),VLOOKUP(A2,A4:B7,2,0))

I guess the trick I missed was having the double-quotes around the Index() function too.

but still not sure why this would be more satisfactory, if at all, to the OP. The result is the same without much added complexity.

**DonkeyOte** · 02-17-2009, 10:27 AM

Re: OP - yes not sure either

CELL is Volatile so I'd opt for the other.

**NBVC** · 02-17-2009, 11:09 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save