Assuming the range contains numbers only (no formulas that return formula
blanks):

=LOOKUP(1E100,1/B3:E3,B3:E3)


--
Biff
Microsoft Excel MVP


"Scott" wrote in message
...
In the range (B3:E3) below, I'm using FORMULA 1 and FORMULA 2 below to
retrieve the last value that is "NonBlank and Not Equal To Zero".

Both FORMULA 1 and FORMULA 2 will both retrieve the last value within the
range that is "NonBlank", but they both return a zero if the last value is
a zero.

I need help to come up with a formula that will return the value "2000" in
the below example, instead of zero. Any ideas?


Example Cells
*************************************************

A B C D E
1
2 4000 2000 0
3
4


FORMULA 1:
***************

=LOOKUP(9^9, B3:E3)



FORMULA 2:
***************

=LOOKUP(2,1/ISNUMBER(B3:E3),B3:E3)