VLOOKUP and HYPERLINK - Go to found cell

[Medimatt]

Hello,

I am trying to use VLOOKUP to find a match and when found create a link to the cell where the match is found. Column A in 'NewOrPropertyIn' and 'Property Out' contain numbers unique to that worksheet. This signals a job has reached a certain stage. 'Property Out' is completed when the job is finished so there is no correlation between the Row the 2 numbers could be found in. So what I want is Column S (NewOrPropertyIn) to contain a formula that looks for the Value in column A (same row) and find a match in Column A (Property Out) then create a hyperlink to take me to that cell with the text "property returned" as the hyperlink text.

I can only get the link to take me to 'Property Out' and I am sure someone will be able to show me where I am going wrong. Here is what I have so far.

=IFERROR(HYPERLINK(VLOOKUP(A2,'Property Out'!$A:A,1,0),"Property Returned"),"")

I have tried a few variations but just get errors or #values and the link stops working altogether.

Thanks in advance.

Matthew

[CK76]

Your formula syntax for Hyperlink is off. You'd need "#'SheetName'!A1" to hyperlink to worksheet cell.

So you'd not use VLookup, but rather MATCH or some other function.
Ex: =IFERROR(HYPERLINK("#'Property Out'!$A"&MATCH(A2,'Property Out'!$A:$A,0),"Property Returned"),"")

[Medimatt]

Thank you for your reply, this is giving the same result as above and taking me to the worksheet but not the cell.

[CK76]

Please upload sample workbook that represent your data set.

[Medimatt]

Please upload sample workbook that represent your data set.

It is working now, I just copied the formula in again and it started working. I assume I missed something earlier! Thanks

[sandy666]

maybe adapt this formula: =HYPERLINK("#"&IFERROR(CELL("address",INDEX('Base of links'!$D$2:$D$6,MATCH(B2,'Base of links'!$A$2:$A$6,0))),""),"whatever")

[CK76]

You are welcome