Find the row number of last "0" value in the column

[motilulla]

Hello,

I am using formula to search last number "Row" in find in the column, which work fine with any number 1 to 9, but having problem with "0" does not give correct row number

For example formula in D6 must return for searched value "0" Row number 45, but it is returning 500, and the formula in E6 returning correct Row number 36 for value 2

Like this...

Book1
	A	B	C	D	E	F
1
2
3				Get Row N	Get Row N
4				For Value	For Value
5			Numbers	0	2
6			4	500	36
7			7
8			5
9			4
10			3
11			1
12			3
13			7
14			6
15			4
16			0
17			5
18			3
19			1
20			7
21			4
22			3
23			5
24			5
25			1
26			2
27			4
28			3
29			5
30			0
31			6
32			3
33			6
34			5
35			1
36			2
37			5
38			4
39			6
40			8
41			4
42			5
43			7
44			1
45			0
46			4
47			3
48			3
49			6
50			4
51			3
52			4
53			4
54			5
55
56
Sheet1

Cell Formulas
Range	Formula
D6	{=MAX(IF(C6:C500=$D$5,ROW(C6:C500)))}
E6	{=MAX(IF(C6:C500=$E$5,ROW(C6:C500)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Please help with correct formula

Thank you all
Excel 2000
Regards,
Moti

 

[Aladin Akyurek]

Try...

=LOOKUP(9.99999999999999E+307, 1/(ISNUMBER(C6:C500)*(C6:C500=0)),ROW(C6:C500))

 

[motilulla]

Try...

=LOOKUP(9.99999999999999E+307, 1/(ISNUMBER(C6:C500)*(C6:C500=0)),ROW(C6:C500))

Aladin Akyurek, it worked nicely!!

Thank you for your kind help

Have a good day

Kind Regards,
Moti

 

[Aladin Akyurek]

Aladin Akyurek, it worked nicely!!

Thank you for your kind help

Have a good day

Kind Regards,
Moti

You are very welcome.