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.

[oeldere]

Maybe I misread but would it not be;

MATCH(INDIRECT(ADDRESS(ROW()+35,COLUMN()-3)),G3:G30),0)

[Immortalis15]

I don't believe so. Ok my example was very poor.

Let's change the example to MATCH(INDIRECT(ADDRESS(ROW(),COLUMN()-3)),G113:G130),0)+112

Whatever is in the cell 3 columns to the left of current INDIRECT(ADDRESS(ROW(),COLUMN()-3)) is what I'm searching for to match in the array G113:G130.

Let's say it matches and returns a value of 5 and then I add 112 to it. In the array 113:130, returning a value of 5 would mean the ACTUAL row it's in is 117. So to get the actual row, no matter what value comes up, I have to add 112 to it.

I hope that clears it up?

[oeldere]

"I don't believe so."

Did you test it?

[Immortalis15]

No, It just doesn't make sense to me.

If I'm working in cell F200 then I want to match cell C200 to something within the array.

From cell F200, INDIRECT(ADDRESS(ROW(),COLUMN()-3)) would refer to C200

Your modification would add 35 rows to it, so would try to match what's in cell C235 instead of C200.

Unless I'm mistaken, but as I had mentioned, my formula works just fine on it's own as a formula, it just doesn't work using it in defining a NAME.

[Immortalis15]

"I don't believe so."

Did you test it?

I tested your theory and it gave me a circular reference warning.

[Immortalis15]

Well, disappointed that noone was able to help but I found a different way to do what I was trying to do.

[Marc L]

You may have more chances next time if you at least create the thread in the appropriate forum …