Get Last NonBlank Value

[Scott]

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)

 

[Dave Peterson]

One way:

=LOOKUP(2,1/((B3:E3<>0)*ISNUMBER(B3:E3)),B3:E3)

 

[muddan madhu]

Try this

=LOOKUP(2,1/(B3:E3>0),B3:E3)

 

[Scott]

I found another way, but I need some help understanding it and your example.
Please example what the "1/(B3:E3)>0) part is. It looks like the value found
is being divided into 1.

What is the logic there?

=LOOKUP(2,1/(B3:E3>0),B3:E3)

 

[T. Valko]

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

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

 

[Dave Peterson]

(b3:e3>0)
returns an array of true's and false's
1/(b3:e3>0)
returns an array of 1's and errors (divided by 0 errors)

the =lookup() is looking for a match for the number 2 in that array of 1's and
errors. Since there are no 2's in that array and since it ignores the errors,
it'll use the last 1 in that array.

Then it uses that "index" into the last range (b3:E3).

But if your data can contain strings (even =""), you may want to do some more
testing--if you really want the last number returned.