Excel: Last non zero value in a row
 

I need to (in formula not VB code) lookup the last non zero value in a row
and return the "header record value" for that column
e.g below - I want to see 27/10 for the first row and 10/11 for the second
row.

Date 27/10/2008 03/11/2008 10/11/2008
27/10/2008 20.5 0 0
10/11/2008 0 $0 $10

Excel: Last non zero value in a row
 

SimmoG wrote...
I need to (in formula not VB code) lookup the last non zero value in a row
....

I'll assume you want to find the last positive numeric value. If you
could have positive or negative values, it's likely you could also
have legitimate zero values.

The row index of the last nonzero value in, say, A3:A10000 could be
found using the array formula

=MATCH2,1/(A3:A100000))

Or you could return that bottommost value using the regular formula

=LOOKUP(2,1/(A3:A100000),A3:A10000)

Excel: Last non zero value in a row
 

On Thu, 14 May 2009 07:37:02 -0700, SimmoG
wrote:

I need to (in formula not VB code) lookup the last non zero value in a row
and return the "header record value" for that column
e.g below - I want to see 27/10 for the first row and 10/11 for the second
row.

Date 27/10/2008 03/11/2008 10/11/2008
27/10/2008 20.5 0 0
10/11/2008 0 $0 $10




=LOOKUP(2,1/(B2:Z2<0),$B$1:$Z$1)

This assumes your headers are in B1:Z1
your data is below that
and you want this formula in A2 (for row 2)

Fill down for subsequent rows.
--ron