Need to find corresponding row 0 value in the header C2:K2

[motilulla]

I need a formula or VBA, which can find corresponding row 0 value in the header C2:K2 and result in the column M</SPAN></SPAN>

For an example... </SPAN></SPAN>
Of the row 8 cell F8=0 so the value of header C2:K2 F2=4 result in the column M8=4 </SPAN></SPAN>
Of the row 9 cell I9=0 so the value of header C2:K2 I2=7 result in the column M9=8.... and so on</SPAN></SPAN>

Here is an example data....</SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2			1	2	3	4	5	6	7	8	9
3
4
5			n1	n2	n3	n4	n5	n6	n7	n8	n9		RESULT
6			1	1	1	1	1	1	1	1	1
7			2	2	2	2	2	2	2	2	2
8			3	3	3	0	1	3	3	3	3		4
9			4	4	4	1	2	4	0	4	4		7
10			5	0	5	2	3	5	1	5	5		2
11			6	1	6	3	4	0	2	6	6		6
12			7	2	7	0	5	1	3	7	7		4
13			8	0	8	1	6	2	4	8	8		2
14			0	1	9	2	7	3	5	9	9		1
15			1	0	10	3	8	4	6	10	10		2
16			2	1	11	0	9	5	7	11	11		4
17			0	2	12	1	10	6	8	12	12		1
18			0	3	13	2	11	7	9	13	13		1
19			1	4	14	0	12	8	10	14	14		7
20			0	5	15	1	13	9	11	15	15		1
21			0	6	16	2	14	10	12	16	16		1
22			1	7	17	3	15	11	0	17	17		7
23			0	8	18	4	16	12	1	18	18		1
24			1	9	19	0	17	13	2	19	19		4
25			0	10	20	1	18	14	3	20	20		1
26			1	11	21	0	19	15	4	21	21		4
27			2	12	0	1	20	16	5	22	22		3
28			0	13	1	2	21	17	6	23	23		1
29			0	14	2	3	22	18	7	24	24		1
30			0	15	3	4	23	19	8	25	25		1
31			0	16	4	5	24	20	9	26	26		1
32			2	0	5	1	3	37	7	27	14		2
33			3	1	6	2	4	0	8	28	15		6
34			4	2	7	3	5	1	9	0	16		8
35			0	3	8	4	6	2	10	1	17		1
36			1	4	9	5	7	0	11	2	18		6
37			2	5	10	6	8	1	12	3	0		9
38			3	6	11	7	0	2	13	4	1		5
39			4	7	12	8	1	3	0	5	2		7
40			5	8	0	9	2	4	1	6	3		3
41			6	0	1	10	3	5	2	7	4		2
42			7	1	2	11	4	0	3	8	5		6
43			8	2	0	12	5	1	4	9	6		3
44			9	3	1	0	6	2	5	10	7		4
45			10	4	2	1	7	3	6	0	8		8
46			11	5	3	0	8	4	7	1	9		4
Sheet2

Thank you all</SPAN></SPAN>

Excel 2000</SPAN></SPAN>
Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[DanteAmor]

Try this option with array formula in M6 an copy down:

{=IFERROR(INDEX($C$2:$K$2,0,MAX(IF(C6:K6=0,COLUMN(C6:K6)))-2),"")} <tbody> </tbody>

<tbody>
</tbody>

 

[DanteAmor]

Another option with regular formula.

=SUMPRODUCT((C6:K6=0)*($C$2:$K$2))

But if there is no 0, a 0 returns.

 

[motilulla]

Try this option with array formula in M6 an copy down:

{=IFERROR(INDEX($C$2:$K$2,0,MAX(IF(C6:K6=0,COLUMN(C6:K6)))-2),"")} <TBODY> </TBODY>

<TBODY>
</TBODY>

DanteAmor, This formula has "IFERROR" function, which is not compatible with excel 2000.

Another option with regular formula.

=SUMPRODUCT((C6:K6=0)*($C$2:$K$2))

But if there is no 0, a 0 returns.

This regular "SUMPRODUCT" formula worked like a charm no problem if result returns 0 in case there is no zero in the row</SPAN></SPAN>

Thank you DanteAmor, for your kind help</SPAN></SPAN>

Kind Regards</SPAN></SPAN>
Moti </SPAN></SPAN>

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.