MATCH function

[hynkal]

Hey everyone,

I am quite new to excel and been trying to find a solution for over a day now.. I guess it's rather an easy problem, however, cannot really understand how to solve it.

Here is a table I have.

A	B	C	D	E	F	G
1	1	0	0	1	1	1

My task is to count ones until certain level (in this instance, 3) and return an exact location after certain level is achieved. For example, 0 means that an employee wasn't present at the job whereas 1 - present at the job. We pay salary after employee has been in the job for 3 days. So, I have to write formula which would return a result of 5 (meaning that employee was present on A and B days, absent on C and D days, and on E day arrived to the job and this was the third day of presence).

I was using formula below but it returns N/A, hopefully someone could help me out.

=+MATCH(3,SUM(J7:N7)=3,0)

 

[AhoyNC]

See if this does what you want. I'm not sure with Excel 2021, but you may have to enter the formula as an array which I think is COMMAND+RETURN on a Mac..

Book1
	A	B	C	D	E	F	G
1	1	1	0	0	1	1	1
2
3	5
Sheet1

Cell Formulas
Range	Formula
A3	A3	=SMALL(IF($A$1:$G$1=1,COLUMN($A$1:$G$1)-COLUMN(INDEX(A1:G1,1,1))+1),3)

 

[hynkal]

See if this does what you want. I'm not sure with Excel 2021, but you may have to enter the formula as an array which I think is COMMAND+RETURN on a Mac..

Book1
	A	B	C	D	E	F	G
1	1	1	0	0	1	1	1
2
3	5
Sheet1

Cell Formulas
Range	Formula
A3	A3	=SMALL(IF($A$1:$G$1=1,COLUMN($A$1:$G$1)-COLUMN(INDEX(A1:G1,1,1))+1),3)

Thank you very much, it works perfectly! Have a nice day!

 

[AhoyNC]

You're welcome. Thanks for the feedback.

 

[Peter_SSs]

Here is another way that you could consider.

22 04 19.xlsm
	A	B	C	D	E	F	G
1	1	1	0	0	1	1	1
2
3	5
3 days

Cell Formulas
Range	Formula
A3	A3	=INDEX(FILTER(SEQUENCE(,COLUMNS(A1:G1)),A1:G1=1),3)

BTW, could you have data like these and, if so, what are the expected results?

22 04 19.xlsm
	A	B	C	D	E	F	G
1	0	0	0	0	1	1	1
2	0	1	0	1	0	0	0
3 days (2)

 

[Peter_SSs]

Minor point but just realised that the "=1" in my previous formula suggestion is not actually required.

22 04 19.xlsm
	A	B	C	D	E	F	G
1	1	1	0	0	1	1	1
2
3	5
3 days

Cell Formulas
Range	Formula
A3	A3	=INDEX(FILTER(SEQUENCE(,COLUMNS(A1:G1)),A1:G1),3)