index match 1st number above 0, only next 10 rows

[ste33uka]

Hi would anyone have formula for index match, that would return 1st value above 0, but only for next 10 rows,
So in examle index match apple,in would return '12'

Book1
	A	B	C	D	E	F	G	H	I
1							apple
2	apple		0
3			12
4			0
5			0
6			7
7			0
8			0
9			16
10			0
11			0
12			0
13			0
14			7
15			0
16			16
17			0
18			0
19			0
20			0
21
22
Sheet1

 

[Toadstool]

Hi Ste33uka,

I don't understand "but only for next 10 rows "? Could you give an example and indicate where you'd like the answer? Thanks!

This returns the first non-zero cell
=INDEX($C$2:$C$20,AGGREGATE(15,6,ROW($C$2:$C$20)-ROW($C$1)/($C$2:$C$20>0),1))

 

[ste33uka]

Answer could be in h1, what i mean is when match is found, only look in next 10 rows, so in example it would only look at rows 2 to 11.
Thanks

Book1
	A	B	C	D	E	F	G	H
1							apple	12
2	apple		0
3			12
4			0
5			0
6			7
7			0
8			0
9			16
10			0
11			0
12			0
13			0
14			7
15			0
16			16
17			0
18			0
19			0
20			0
21
Sheet1

Cell Formulas
Range	Formula
H1	H1	=INDEX($C$2:$C$20,AGGREGATE(15,6,ROW($C$2:$C$20)-ROW($C$1)/($C$2:$C$20>0),1))

 

[Toadstool]

It seems like you're saying it's always 2 to 11 but I'm guessing maybe you only want to start in the row where G1 is found in column A?

This would do that for you

Ste33uka.xlsx
	A	B	C	D	E	F	G	H
1							apple	12
2	apple		0
3			12
4			0
5			0
6			7
7			0
8			0
9			16
10			0
11			0
12			0
13			0
14			7
15			0
16			16
17			0
18			0
19			0
20			0
Sheet1 (2)

Cell Formulas
Range	Formula
H1	H1	=INDEX(C:C,AGGREGATE(15,6,ROW(OFFSET($C$1,MATCH($G$1,$A$2:$A$9999,0),,10))/((OFFSET($C$1,MATCH($G$1,$A$2:$A$9999,0),,10)>0)),1))

 

[Peter_SSs]

.. another version

20 08 15.xlsm
	A	B	C	D	E	F	G	H
1							apple	12
2	apple		0
3			12
4			0
5			0
6			7
7			0
8			0
9			16
10			0
11			0
12			0
13			0
14			7
15			0
16			16
17			0
18			0
19			0
20			0
1st > 0

Cell Formulas
Range	Formula
H1	H1	=INDEX(OFFSET(INDEX(C:C,MATCH(G1,A:A,0)),,,10),MATCH(TRUE,OFFSET(INDEX(C:C,MATCH(G1,A:A,0)),,,10)>0,0))

 

[ste33uka]

Thanks for both suggestions
appreciate it

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.