"Aladin Akyurek" wrote...
You appear to be looking for the last non-zero value...

=LOOKUP(2,1/(ABS(E1:INDEX(E1:E10,MATCH(9.99999999999999E+307,
E1:E10)))0),E1:E10)
....

I haven't tested, but LOOKUP doesn't mind if its 2nd and 3rd arguments are
different size? Why would this be better than the simpler

=LOOKUP(2,1/(-E1:E10<0),E1:E10)

?