how to return the next result > zero

[leslieann]

Hello all,

I can't seem to be able to get the formula to return the next number up in a column that has a number > zero. For example:

B11 must return the value in G10 unless the value in G10 is zero, in which case, B11 should then return the value in G9, unless G9 is zero, then B11 must return the value in G8. There should, theoretically not be more than 2 zero's in a row in column G, so the formula for B does not need to be infinite.

HELP!!

[Sam Capricci]

does it end at B11 or can it be B100 and look all the way up to G2?
this would handle it for B11
=IF(G10>0,G10,IF(G9>0,G9,IF(G8>0,G8,IF(G7>0,G7,IF(G6>0,G6,IF(G5>0,G5,IF(G4>0,G4,IF(G3>0,G3,IF(G2>0,G2,"")))))))))

[JohnTopley]

Try

=LOOKUP(999999,1/(G2:G11<>0),G2:G11)

[leslieann]

That was perfect, thank you!!!

[Sam Capricci]

you're welcome and thank you for the rep! BTW, I liked John's better.