First cell with a value of 0 after a certain row

[Hipsterpotamus]

I need to determine the range of values from Column A in my dataset where the corresponding values values in Column B are not equal to 0. Finding the the first value (in this example, 7) is easy enough, but the last value is giving me some issues. Anyone have any advice on how to solve this problem?

Thank you!

Book1.xlsx
	A	B	C	D	E
1	1	0		First	Last
2	2	0		7
3	3	0
4	4	0
5	5	0
6	6	0
7	7	1
8	8	5
9	9	7
10	10	10
11	11	5
12	12	6
13	13	0
14	14	0
15	15	0
16	16	0
17	17	0
18	18	0
19	19	0
20	20	0
Sheet4

Cell Formulas
Range	Formula
D2	D2	=INDEX(A1:A99998,MATCH(TRUE,INDEX(B:B>0,0),))

 

[hagia_sofia]

Hello, how about:

=TRANSPOSE(TAKE(FILTER(A1:A20,B1:B20<>0),{1,-1}))

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E
1	1	0		First	Last
2	2	0		7	12
3	3	0
4	4	0
5	5	0
6	6	0
7	7	1
8	8	5
9	9	7
10	10	10
11	11	5
12	12	6
13	13	0
14	14	0
15	15	0
16	16	0
17	17	0
18	18	0
19	19	0
20	20	0
Sheet5

Cell Formulas
Range	Formula
D2:E2	D2	=XLOOKUP(TRUE,B1:B1000>0,A1:A1000,,,{1,-1})
Dynamic array formulas.

 

[Hipsterpotamus]

Thank you for the responses, but neither solution quite work.

I used a bad example in my first post. The full data has multiple ranges where the values are greater than 0. So both formulas would give me the value of the last cell with a value greater than 0 in the whole data set, not the range.

I'll post a better example below.

Book1.xlsx
	A	B	C	D	E
1	1	0		First	Last
2	2	0		7
3	3	0
4	4	0
5	5	0
6	6	0
7	7	1
8	8	5
9	9	7
10	10	10
11	11	5
12	12	6
13	13	0
14	14	0
15	15	0
16	16	0
17	17	0
18	18	0
19	19	0
20	20	0
21	21	8
22	22	15
23	23	36
24	24	5
25	25	0
26	26	0
27	27	0
28	28	0
29	29	0
30	30	0
31	31	8
32	32	7
33	33	0
34	34	0
35	35	0
Sheet4

Cell Formulas
Range	Formula
D2	D2	=INDEX(A1:A99998,MATCH(TRUE,INDEX(B:B>0,0),))

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E
1	1	0		First	Last
2	2	0		7	12
3	3	0
4	4	0
5	5	0
6	6	0
7	7	1
8	8	5
9	9	7
10	10	10
11	11	5
12	12	6
13	13	0
14	14	0
15	15	0
16	16	0
17	17	0
18	18	0
19	19	0
20	20	0
21	21	8
22	22	15
23	23	36
24	24	5
25	25	0
26	26	0
27	27	0
28	28	0
29	29	0
30	30	0
31	31	8
32	32	7
33	33	0
34	34	0
35	35	0
Sheet5

Cell Formulas
Range	Formula
D2:E2	D2	=LET(x,XLOOKUP(TRUE,B1:B1000>0,A1:A1000),HSTACK(x,INDEX(x:A1000,XMATCH(0,INDEX(B:B,ROW(x)):B1000)-1)))
Dynamic array formulas.

 

[Hipsterpotamus]

That works. Now I gotta figure out how to use it for multiple ranges. Thank you!!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[hagia_sofia]

One more option:

=LET(
col_1,A1:A35,
col_2,B1:B35,
a,SCAN(0,col_2,LAMBDA(x,y,IF(y=0,0,x+1))),
b,SCAN(0,a,LAMBDA(x,y,IF(y=1,x+1,x))),
c,IF(col_2=0,0,1),
d,b*c,
WRAPROWS(DROP(REDUCE("",DROP(UNIQUE(d),1),LAMBDA(a,b,VSTACK(a,TAKE(FILTER(col_1,d=b),{1,-1})))),1),2))

 

[Fluff]

That just returns #VALUE! for me.

 

[hagia_sofia]

That just returns #VALUE! for me.

I do not why but I guess it does not matter since your solution is ok.