Lookup the last value in a column that is larger than 0

[Asaflahav1]

Hi guys,

I was looking for a solution for this for a long time now, and didn't noticed it in the forums, maybe you can be an assistance:

what I'm trying to do is to lookup a certin column last value that is larger than 0. for example, if I'm trying to search this column:

A1 4
A2 5
A3 0
A4 2
A5 0

I would like to receive the value "2".
Now, I've tried to think of a combination of an "LOOKUP(9.99999e+10,307)" function with an "IF(">0") array function, but couldn't come up with anything.

Now, I prefer not using VBA on this one, and I can't just replace the zeros in the columns with "".
Any suggestions?

 

[Tigs81]

Hi Asaflahav1

To do this add a column in front of A1

Then add this formula and fill down the list. at the bottom of the list eg in cell A6 add false

=IF(A2="","",IF(AND(A2=FALSE,B1>0),TRUE,IF(A2=TRUE,"",FALSE)))

then use a vlookup like

=VLOOKUP(TRUE,A1:B5,2,FALSE)

Thanks
Tigs

 

[niceguy_jay]

use this formula anywhere and it will return you the counts

=COUNTIFS(A:A,"0")

 

[Aladin Akyurek]

Hi guys,

I was looking for a solution for this for a long time now, and didn't noticed it in the forums, maybe you can be an assistance:

what I'm trying to do is to lookup a certin column last value that is larger than 0. for example, if I'm trying to search this column:

A1 4
A2 5
A3 0
A4 2
A5 0

I would like to receive the value "2".
Now, I've tried to think of a combination of an "LOOKUP(9.99999e+10,307)" function with an "IF(">0") array function, but couldn't come up with anything.

Now, I prefer not using VBA on this one, and I can't just replace the zeros in the columns with "".
Any suggestions?

Try...

=LOOKUP(9.99999999999999E+307,1/A1:A5,A1:A5)

 

[Asaflahav1]

Wow! All works! Thanks guys!