Excel: Last non zero value in a row

[SimmoG]

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

 

[Harlan Grove]

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:A10000>0))

Or you could return that bottommost value using the regular formula

=LOOKUP(2,1/(A3:A10000>0),A3:A10000)

 

[Harlan Grove]

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:A10000>0))

Or you could return that bottommost value using the regular formula

=LOOKUP(2,1/(A3:A10000>0),A3:A10000)

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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