Define Name with match and indirect

**Immortalis15** · 05-20-2020, 02:31 PM

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** · 05-20-2020, 04:01 PM

Maybe I misread but would it not be;

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

**Immortalis15** · 05-20-2020, 04:41 PM

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** · 05-20-2020, 04:45 PM

"I don't believe so."

Did you test it?

**Immortalis15** · 05-20-2020, 05:05 PM

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** · 05-21-2020, 01:20 PM

Originally Posted by oeldere

"I don't believe so."

Did you test it?

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

**Immortalis15** · 05-22-2020, 08:17 PM

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

**Marc L** · 05-22-2020, 10:28 PM

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

Define Name with match and indirect

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Define Name with match and indirect

Re: Define Name with match and indirect

Re: Define Name with match and indirect

Re: Define Name with match and indirect

Re: Define Name with match and indirect

Re: Define Name with match and indirect

Re: Define Name with match and indirect

Thread Information

Users Browsing this Thread

Similar Threads

Is there a better/more efficient way to use Indirect, sumproduct and Define Name

Vlookup with indirect to define the array

[SOLVED] Data Validation & Define Name & Indirect function

[SOLVED] Nest mutiple indirect functions to define a range.

[SOLVED] how do I define a chart series with an indirect reference

[SOLVED] Define chart range using indirect reference

Define Name problem using INDIRECT with ROW()

Bookmarks

Bookmarks

Posting Permissions