Hyperlink formula to row after last used row in table

[sspatriots]

Trying to combine two first two formulas below makes my Hyperlink to another table's next available row results in the error message "Reference isn't valid".


The formula below is in cell N1 of another worksheet and provides the row number of the next available row in the table I'm trying to link to:

MATCH(LOOKUP(2,1/(G2JobList[[#Data],[Job Name]]<>""),G2JobList[[#Data],[Job Name]]), G2JobList[[#Data],[Job Name]], 0)+1

The hyperlink formula below references cell "N1" for the row number to take the user to the next available blank row in a table on another worksheet.

=HYPERLINK("#" & "OFFSET(G2JobList[[#Headers],[Job Name]],N1,0)","Go to 'Job List'")

All of the above works for the hyperlink when used separately, however, I would like to have everything in one formula. Therefore, I substituted 'N1' in the second formula with the first formula. After I combine them, I get the error message 'Reference isn't valid'. Just wondering if there is something else I should have done to combine these two formulas to make them work like they do when they are two separate formulas.

=HYPERLINK("#" & "OFFSET(G2JobList[[#Headers],[Job Name]],MATCH(LOOKUP(2,1/(G2JobList[[#Data],[Job Name]]<>""),G2JobList[[#Data],[Job Name]]), G2JobList[[#Data],[Job Name]], 0)+1,0)","Go to 'Job List'")

Thanks, SS

 

[sspatriots]

In case anyone in need finds use of this, below is the formula I ended up with. Seems to work well:

=HYPERLINK("#" & CELL("address", OFFSET(XLOOKUP(LOOKUP(2,1/(Job_Name<>""),Job_Name), Job_Name, Job_Name,,0,-1), 1, 0)), "Go to 'Job List'")