Column number where all values match

[cs56lioy]

Given this array

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

I would like to return the number 2 for the column number where both rows contain a 1.
I tried

XMATCH(1,{0,1,0,0;0,1,0,0},0)

and even

XMATCH({1;1},{0,1,0,0;0,1,0,0},0)

but none of them worked.

Any help would be appreciated

 

[Arts]

Is this what you mean ?

0	1	0	0	0	1	0	0
	2				2

If so

=IF(--(B3:I3=1),2,"")

 

[cs56lioy]

It is more like

0	1	0	0
0	1	0	0

You can see that the 2nd. column has the 1's, so I need the number of the column that has both 1's returned

 

[Cubist]

Try

=XMATCH(TRUE,MMULT({1,1},{0,1,0,0;0,1,0,0})=2)

 

[Arts]

Apologies for the delay was called away, I'm sure Cubist formula does what you need (going by help I have received on previous posts his excel knowledge is far superior than mine!) Just curious are the 1's always in the second column or could you have 1's in different columns ?

If so you could try the below that will give you anything that has multiple "1"'s across various columns (If I've understood what you mean).

Adjust ranges to suit

=IF(SUM(C3:C22)>=2,COLUMN(),"")

3	5	7
0	1	0	0	0	1	0	0
0	1	0	1	0	1	0	0
0	1	0	1	0	1	0	0

 

[Scott Huish]

I'm assuming you aren't actually using a hard-coded array, so along the line of what Arts is suggesting:

=MATCH(2,A1:D1+A2:D2,0)

 

[cs56lioy]

Thanks Cubist. That worked.

Thanks Arts for you help.
To clarify, the array has 2 rows and I am looking for the column that has a 1 in both rows. There could be 1's elsewhere. Nothing is hardcoded, other than that there are 2 rows.

 

[cs56lioy]

Just for the record, the following also worked

=XMATCH(TRUE,1&1={0,1,0,0}&{0,1,0,0},0)