Now I need to look from E668 right to
get the first non-zero value.

You subject line says last non-zero but your description says first
non-zero. So, which is it?

Assuming the range contains only numbers or possibly empty cells.

For the first non-zero (array entered**):

=INDEX(A1:E1,MATCH(TRUE,A1:E1<0,0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

For the last non-zero:

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

--
Biff
Microsoft Excel MVP


"mjones" wrote in message
...
Hi All,

Someone gave me this formula years ago and it works great. It gets
the value of the last non-zero in a column.

Now, I need to turn it around and get the last non-zero value in a
row, but I can't figure it out. Here's the old formula:

=LOOKUP(9.99E+307,IF(Bank!H9:H65536=0,"",Bank!H9:H 65536))

It looked from row H down. Now I need to look from E668 right to get
the first non-zero value.

Any help would be appreciated.

Thanks

Michele