Excel equation to find last non-zero value in column (must look at all the values in column)

[cching]

Hello,

I currently have an equation that will give me the last non-zero value in the column. However, my equation works by looking through the column from the top, and once it sees a 0 it will stop and output the value above it instead of going through the entire list.

Example

[TABLE="width: 133"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD="align: right"]26[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD="align: right"]27[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD="align: right"]28[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD="align: right"]29[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD="align: right"]31[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD="align: right"]32[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD="align: right"]33[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD="align: right"]34[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]36[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]37[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]38[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]39[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

The equation i have is

=INDEX(B2:B40,MATCH(0,B2:B40,0)-1)

which will output "1" since it found the "0" at row 13, and outputted the value in row 12.

How to I get it so that it will look through the entire column and output the last non-zero value, in this case, it should be "13"

Thanks

 

[Weazel]

maybe something like...


=LOOKUP(2,1/(B2:B40<>0),B2:B40)

 

[Oaktree]

Try =LOOKUP(2,1/(B2:B40<>0),B2:B40)

 

[cching]

Thanks Guys! just what I needed!