Return row number from an array/name range

[PrashanthKumar123]

hi - In my project, I am encountering a array/name range like the below with 0s and 1s. There maybe multiple 1s in a single row, but all other rows will only have 0s. I am looking for a formula that returns the row number which has the 1s, i.e., in this case, row 2. Is there a way to do this?

Once I have the row number, I plan to use this in an index function.

0​	0​	0​	0​
0​	1​	1​	1​
0​	0​	0​	0​
0​	0​	0​	0​
0​	0​	0​	0​

 

[Fluff]

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’)

Maybe

+Fluff 1.xlsm
	A	B	C	D	E	F
1	0	0	0	0
2	0	1	1	1
3	0	0	0	0		2
4	0	0	0	0
5	0	0	0	0
Data

Cell Formulas
Range	Formula
F3	F3	=AGGREGATE(15,6,(ROW(A1:D5)-ROW(A1)+1)/(A1:D5>0),1)

 

[PrashanthKumar123]

That simple! I tweaked this a little but it works with Aggregate function. Thank you very much!

 

[mikerickson]

If your range is MyRange and there are 0 or one in every cell of myRange, try

=MAX(ROW(myRange)*myRange)

 

[Fluff]

That simple! I tweaked this a little but it works with Aggregate function. Thank you very much!

You're welcome & thanks for the feedback.