Hyperlink missing by set number of rows

[drewberts]

I have 2 tables on separate sheets that each has its header starting at row 5. I have a formula that hyperlinks from a cell in one sheet to a corresponding cell in another sheet based on a value in a column on my first sheet. I'm using structured references in my formula which maybe the problem but I’m not sure if that’s the case.

This is the formula I'm using

=IF(ISERROR((HYPERLINK("#'Tender Calc'!" & ADDRESS(MATCH([@TTenderNo1], (TenderCalcTable[TCTenderNo1]), 0), 1), [@TTenderNo1]))),[@TTenderNo1],(HYPERLINK("#'Tender Calc'!" & ADDRESS(MATCH([@TTenderNo1],(TenderCalcTable[TCTenderNo1]), 0), 1), [@TTenderNo1])))

Sheet 1 is called Tenders
Sheet 2 is called Tender Calc

The formula uses the value in sheet 1 (on the same row) from column TTenderNo1 to hyperlink to the same number found in column TCTenderNo1 on sheet 2. The problem is that when the hyperlink is clicked, it selects a cell on sheet 2 which is in the correct column but 5 cells above the correct one.

It’s clearly to do with the table starting at Row 5 and I don't want to change this. It seems to work fine if I change my structured references to absolute cells references but I’m trying to make the whole thing dynamic.

Can anyone shed any light on how to fix the issue? I'm thinking ROW() may be the solution here, but I'm stumped with the syntax.

Thanks

 

[RasGhul]

I think the address( maybe forcing the incorrect row, whereas Match alone should return the correct row number if the match array is the right size?