find last non-zero entry

  • Thread starter Thread starter Bruce Bowler
  • Start date Start date
B

Bruce Bowler

I have an array in my spreadsheet, O7:P33. I'd like to find the value in
P that corresponds to the last non-zero entry in O (O is actually a bunch
of formulas in the event it matters). It seems to me that one of the
various LOOKUP functions should do the trick, but I can't seem to make
any of them work...

Any suggestions??

Thanks!
Bruce
 
One of the 2 array-entered** expressions below should yield the
desired result for you, depending on what you mean exactly by "Last
non-zero entry in col O"
**press CTRL+SHIFT+ENTER to confirm the formula

If you mean "greater than zero":
=INDEX(P$7:P$33,MATCH(MAX(IF(O$7:O$33>0,ROW(O$7:O$33))),IF(O$7:O
$33>0,ROW(O$7:O$33)),0))

If you mean "not equal to zero":
=INDEX(P$7:P$33,MATCH(MAX(IF(O$7:O$33<>0,ROW(O$7:O$33))),IF(O$7:O
$33<>0,ROW(O$7:O$33)),0))
 
=LOOKUP(2,1/(O7:O33<>0),P7:P33)
Click to expand...

Ron, could you please explain what the formula does? I never used
lookup in this way and I am very interested in the logic behind your
solution.
Thanks!
Cecilia
 
I've seen it documented some place, but I can't recall where.

When lookup_value is greater than ANY of the results, lookup will
return the LAST match.  This also depends on sorting, but that's not
relevant for our purposes.

Bearing that in mind:

o7:33<>0  returns an array of TRUE and FALSE depending on the contents
of those cells.

1/(07:033) returns an array of either 1 or DIV/0

We can now use any value greater than 1 to be higher than any of the
values in the lookup vector.

Since 2 is greater than 1, Lookup will match the last value that is <>
0, and return the corresponding value from the result vector.- Hide quoted text -

- Show quoted text -
Click to expand...

Perfectly understand it now, learned something new, thanks a lot Ron!!
 
Back
Top