Formula to find first non-zero value in series?

[Timd]

Hello. I have a row of cells containing non-zero integers, followed by
several cells containing zero. I would like a function that can be
placed at the end of the row, which will begin looking from the right-
hand side, and find the first non-zero cell, and display the value in
that cell.

For example:

216 6548 32151 215 21 518 0 0 0 0 0

The wanted formula would display 518. If the zero to the right of 518
were changed to a number, it would display that number.

I can write a long series of nested IF statements, but is there a
better way?

Thanks in advance!

Tim, Phoenix, AZ

 

[T. Valko]

Try this:

=LOOKUP(1E100,1/A1:K1,A1:K1)

 

[Timd]

Try this:

=LOOKUP(1E100,1/A1:K1,A1:K1)

Thanks Biff, worked great. You really are an Excel MVP!

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Try this:

=LOOKUP(1E100,1/A1:K1,A1:K1)

Thanks Biff, worked great. You really are an Excel MVP!

 

[victrix]

Lovely, works fine, but how should the formula look like to find the value in
the opposite direction (from left to right)

expl:

0 0 0 4 6 8 9 0 0 formula returns: 4

Thanks in advance

 

[Dave Peterson]

One way (until Biff comes back):
=INDEX(A1:K1,MATCH(TRUE,A1:K1<>0,0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)