Count # of cells to the left of a value

[Jing]

Hey Guys,

it has been a while since i have been here but i'm stuck on a formula. I have the following formula that finds the first instance in a range that is greater than a goal / target value =INDEX($C1:$O1,MATCH(TRUE,INDEX($C1:$O1>$A1,0),)). with the it would return 30 and 75 respectively. What i am trying to find out is a count of the # of cells to the left of the value in the range. so in the case of Row 1 the count would be 6 as there are 6 cells from column C:H. and row 2 would = 9.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]25[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]30[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]60[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]75[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]
Any help would be greatly appriciated.

Thank you in advance.

 

[sheetspread]

Match gives you the answer, just get rid of the first index function.

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	25	6	2	0	0	0	0	30	0	1	10	0	0	0	0
2	60	9	3	5	0	0	0	0	0	0	75	4	5	0	0
Sheet7

Cell Formulas
Range	Formula
B1	=MATCH(TRUE,INDEX($C1:$O1>$A1,0),)

 

[Special-K99]

Maybe

=MATCH(TRUE,INDEX($C1:$O1>$A1,0),)

 

[Jing]

HAHA really overlooking the obvious.

thank you so much

=IFERROR(MATCH(INDEX($E1:$P1,MATCH(TRUE,INDEX($E1:$P1>$B1,0),)),E1:P1,0),0)

 

[sheetspread]

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	25	6	2	0	0	0	0	30	0	1	10	0	0	0	0
2	60	9	3	5	0	0	0	0	0	0	75	4	5	0	0
Sheet7

Cell Formulas
Range	Formula
B1	{=MATCH(TRUE,$C1:$O1>A1,0)}
Press CTRL+SHIFT+ENTER to enter array formulas.

works too

 

[Jing]

you guys are amazing.

Thank you so much