find last non-zero entry

[Bruce Bowler]

I have an array in my spreadsheet, O733. 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

 

[Max]

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$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$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))

 

[Bruce Bowler]

=LOOKUP(2,1/(O7:O33<>0),P733)

Thanks Ron,

Works like a champ!

Bruce

 

[Chechu]

=LOOKUP(2,1/(O7:O33<>0),P733)

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

 

[Chechu]

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 -

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

 

[Ziggy]

You're welcome.  Thanks for the feedback.

Thank you also from another interested bystander