Using Indirect(Address instead of actual range in an Index,Match(TRUE formula

[jerry12302]

My formula below, in range V5:V1004, returns the next non-zero value in column U. There are currently 1,000 rows of data, from U5 to U1004.

=INDEX(U5:U$1200,MATCH(TRUE,INDEX(U5:U$1200<>0,),0))

The formula works fine, it's copied down through column V next to the data, and always finds the next non-zero value between the row it's in through the last row of data.

I plugged 1200 for the last row in the formula, but if the data range grows beyond 1200 I would have to change it. I changed it to 10000, for a test, but someday it may even grow beyond 10000. The problem becomes a matter of slowing down the calculations if it's changed too high, 1,000,000 for example, and if I end up with several of these columns of formulas it wouldn't be practical to use very large numbers for the last row for this reason.

I decided to change the formula to use Indirect(Address instead, with a count of all used data in column U, so I would never have to worry about changing the formula to accommodate larger and larger data sets:

=INDEX(INDIRECT(ADDRESS(ROW(U5),COLUMN(U5))&":"&ADDRESS(ROW(U$4)+COUNT(U:U),COLUMN(U5))),MATCH(TRUE,INDEX(INDIRECT(ADDRESS(ROW(U5),COLUMN(U5))&":"&ADDRESS(ROW(U$4)+COUNT(U:U),COLUMN(U5)))<>0,),0))

The formula returns an error though, #N/A.

The address portion by itself works fine, if I test it =ADDRESS(ROW(U5),COLUMN(U5))&":"&ADDRESS(ROW(U$4)+COUNT(U:U),COLUMN(U5)) I get $U$5:$U$1004

I use Indirect(Address a lot for other purposes, =SUM(INDIRECT(ADDRESS(ROW(U5),COLUMN(U5))&":"&ADDRESS(ROW(U$4)+COUNT(U:U),COLUMN(U5)))) gives me the sum of all the values in column U for example, no problem.

Why can't I use it in the Index,Match context? I simply replaced U5:U$1200 with the Indirect(Address equivalent.

Is there another way to accomplish this without using VBA, and without using Array formulas?

 

[Fluff]

What version of Excel are you suing?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Why not just use

=INDEX(U5:U1200,MATCH(TRUE,INDEX(U5:U1200<>0,),0))

Using Indirect & address is more likely to kill your workbook

 

[jerry12302]

What version of Excel are you suing?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Why not just use

=INDEX(U5:U1200,MATCH(TRUE,INDEX(U5:U1200<>0,),0))

Using Indirect & address is more likely to kill your workbook

I appreciate the reply, and I will update my Account Details, but the whole point of my post is that I don't want to have to keep changing the number of rows as the data set becomes very large, and using very large row numbers in every formula slows down the calculation process. Using 1,000,000 for the rows, for example, slows the file to a crawl, even if there are only 1,000 actual formulas.

I am using Office Home and Business 2020, Excel version 14.... (32 bit)

 

[Fluff]

But unless you can have more ~1200 contiguous blank cells that formula will work & does not need the range changing.

 

[jerry12302]

But unless you can have more ~1200 contiguous blank cells that formula will work & does not need the range changing.

It's not about how many contiguous blank cells are in the data range, or zeros, there may never be more than one or two between non-zero values, but if the data extends beyond row 1200, say to 1210, and there are zeros there, my original formula would need to be changed to accommodate the newer rows of data.

 

[Fluff]

No it will not need to be changed, if you remove the $ from the row number as I suggested, then the formula will always be looking at 1195 rows below where it is. Why not try it & see.

 

[jerry12302]

No it will not need to be changed, if you remove the $ from the row number as I suggested, then the formula will always be looking at 1195 rows below where it is. Why not try it & see.

I understand that technically that would work, but I or another user would always have to initialize it with a legitimate starting point, representing a row value that represents where the current last row of data exists in a particular file. This formula would be copied to other files with data in them, and some files may only have 1200 rows of data, others 10000, or 50, or 80000, etc. A simpler universal solution would be my Indirect(Address approach, if it would work in this context, but it doesn't.

My question is why not, and is there another different approach that can be used without hard inputting the current last row of data.

 

[Fluff]

but I or another user would always have to initialize it with a legitimate starting point, representing a row value that represents where the current last row of data exists in a particular file

That's the whole point, you don't need to do that. As the formula is fill down the last row in the formula will move down with it.
Using Indirect/address will make the formula volatile, so that it will re-calculate whenever any cell in any open workbook is changed or re-calculates.

 

[jerry12302]

That's the whole point, you don't need to do that. As the formula is fill down the last row in the formula will move down with it.
Using Indirect/address will make the formula volatile, so that it will re-calculate whenever any cell in any open workbook is changed or re-calculates.

You're obviously not reading my posts completely, please re-read my last response, especially the part about initiating the row value in different files to suit their particular data size needs. With my Indirect(Address method that step is eliminated entirely. I just want to know why it won't work in this context, maybe it needs to be modified. And I would like to know if there is another approach without inputting a row number.

If you don't know the answer, just admit that, or don't reply again. I don't appreciate someone continuing to answer with the same suggestion, as if I don't understand. I hear you, I understand your point, I don't believe your answer is the only or best way to approach my problem. Maybe someone else can provide a different answer that works.

 

[Fluff]

My answer is not the only way, but it is better than using indirect when there is no need. INDIRECT – Excel’s Most Evil Function

I am reading what you say, but I think you are not listening to me, or have simply rejected my idea out of hand because it's not what you think you need.
You do NOT need to give the formula the last row in the data. If you will never have more than 20 contiguous blank cells you could just as easily use

=INDEX(U5:U35,MATCH(TRUE,INDEX(U5:U35<>0,),0))

As seen here

Fluff.xlsm
	U	V
1
2
3
4
5	1	1
6		2
7		2
8		2
9		2
10	2	2
11		3
12		3
13		3
14		3
15	3	3
16		4
17		4
18	4	4
19	5	5
20		6
21		6
22		6
23		6
24		6
25		6
26		6
27		6
28	6	6
29		7
30		7
31		7
32		7
33		7
34		7
35		7
36		7
37	7	7
38
Main

Cell Formulas
Range	Formula
V5:V37	V5	=INDEX(U5:U35,MATCH(TRUE,INDEX(U5:U35<>0,),0))

In what way does this not do what you want?