How to get position of value within 2-dim array with formula?

[Fractalis]

Hi to all,

I have a formula that has an 1-dim array like this:

{0,0,0,0,0,0,1,1,0,1,0,0,0,1,1,1}

and if I want to search "1", the result is position is 7.

=MATCH(1,{0,0,0,0,0,0,1,1,0,1,0,0,0,1,1,1},0) = 7

now, if I have a 2-dim array (4x4), is there a way to look the position of first "1"?

0,0,0,0
0,0,1,1   ---> 2nd row, position 3
0,1,0,0
0,1,1,1

Is this case, the first "1" would be in position 3 of "second line". So, is the answer could be 2,3 or only 3 since I'm more interested in column position. Currently with MATCH I get #N/A

=MATCH(1,{0,0,0,0;0,0,1,1;0,1,0,0;0,1,1,1},0) =#N/A

Thanks in advance.

 

[MagnarH]

Are the arrays exactly as you present them or is it a range of cells?

 

[Fractalis]

Are the arrays exactly as you present them or is it a range of cells?

Actually is how it looks a long formula after evaluate several steps. The final formula looks like this one with match and a 2D array.

 

[Alex Blakenburg]

If you only want the column, this might work for you.

=MOD(FIND(1,TEXTJOIN("",TRUE,C7:F9)),4)

Where 4 is the number of columns and C7:F9 will be your array of 1s and 0s,

 

[Alex Blakenburg]

Correction to the above should be:

=MOD(FIND(1,TEXTJOIN("",TRUE,C7:F9))-1,4)+1

 

[Fractalis]

Correction to the above should be:

=MOD(FIND(1,TEXTJOIN("",TRUE,C7:F9))-1,4)+1

Thanks so much Alex, it seems to work pretty fine.

In this case the number of columns are 4, then If I want to generalize the number of columns should be like this?

=MOD(FIND(1,TEXTJOIN("",TRUE,C7:F9))-1,COLUMNS(C7:F9))+1

 

[Fractalis]

Correction to the above should be:

=MOD(FIND(1,TEXTJOIN("",TRUE,C7:F9))-1,4)+1

Thanks so much Alex, it seems to work pretty fine.

In this case the number of columns are 4, then If I want to generalize the number of columns should be like this?

=MOD(FIND(1,TEXTJOIN("",TRUE,C7:F9))-1,COLUMNS(C7:F9))+1

 

[Alex Blakenburg]

In this case the number of columns are 4, then If I want to generalize the number of columns should be like this?

You didn't show how your array is being generated but in principle that should work.

PS: thanks for the feedback and glad we could help.