formula required to get value of a range

[hsandeep]

In A2 I need a formula to get value of range C2:L2 for which C3:L3 is ZERO "0"
At a time there will be only 1 0 in C3:L3

Thanks in advance for the help.

Sheet1

A

B

C

D

E

F

G

H

I

J

K

L

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"><col style="WIDTH: 40px"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]7[/TD]

[TD="align: right"]200[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

</tbody>

 

[pagrender]

Hi there,

Does this work for you?
=INDEX($C$2:$L$2,MATCH(0,$C$3:$L$3,0))

 

[sheetspread]

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L
1	2	7	200	100	50	7	8	51	11	12	2	400
2	3		1	1	1	0	1	1	1	1	1	1
Sheet7

Cell Formulas
Range	Formula
B1	=SUMIF($C$2:$L$2,0,$C$1:$L$1)

 

[hsandeep]

NO Sir, I didn't!

 

[hsandeep]

This also DIDN't worked

 

[Aladin Akyurek]

What does "didn't" mean? Wrong value, an error value?

 

[sheetspread]

This also DIDN't worked

Both formulas get you the number above zero (7)

???

 

[hsandeep]

I am extremely sorry, the formula WORKS. I re-checked it right now once again....& found OK

 

[hsandeep]

The range C3:L3 is either 1 or 0. The formula is multiplying all values wherever it gets 0 (there may be more than 1 0 in C3:L3). I need 'last' 0 (on the RHS when considering C3:L3) generated to be used.

 

[Aladin Akyurek]

The range C3:L3 is either 1 or 0. The formula is multiplying all values wherever it gets 0 (there may be more than 1 0 in C3:L3). I need 'last' 0 (on the RHS when considering C3:L3) generated to be used.

Try...

=LOOKUP(9.99999999999999E+307,1/ISERROR(1/C3:L3),C2:L2)