Define Name with match and indirect

[Immortalis15]

What I'm trying to do is to create a formula that searches for a match in an array and if it finds it, returns the row number but adds an offset of 35 and then use that data as part of a cell reference in a formula.

So if the match = 5, it'll add 35 to it to make it 40 and then in a formula would reference cell J40.


Indefining the name JUMP1, I've entered this

MATCH(INDIRECT(ADDRESS(ROW(),COLUMN()-3)),G113:G130),0)+35

This works as a stand alone formula, but doesn't seem to work when I use it to define a name. When I try, it adds the sheet name and changes it to this.

MATCH(INDIRECT(ADDRESS(ROW(),COLUMN()-3)),May!G113:G130),0)+35

Which I don't think should be a problem. But then when I try to use the name in my formula...

IF(AND((EDATE(INDIRECT("J"&JUMP1),1)>=(C45)),(EDATE(INDIRECT("J"&JUMP1),1)<=(C45)+6)),DATE(YEAR(C45),MONTH(C45),DAY(EOMONTH(C45,0))),INDIRECT("J"&JUMP1)),IF(AND((EDATE(INDIRECT("J"&JUMP1),1)>=(C45)),(EDATE(INDIRECT("J"&JUMP1),1)<=(C45)+6)),EDATE(INDIRECT("j"&JUMP1),1),INDIRECT("j"&JUMP1)))

... it doesn't work and even just testing the name with =Jump1 doesn't seem to work.

I realize this may be complicated AF, I barely understand it myself lol. But does anyone have any thoughts on how to solve this?


Thanks in advanced.