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

Column A	Column B
1	11
2	11
3	10
4	9
5	8
6	7
7	6
8	5
9	4
10	3
11	2
12	1
13	0
14	8
15	8
16	8
17	8
18	8
19	8
20	12
21	13
22	12
23	13
24	12
25	13
26	13
27	13
28	13
29	13
30	13
31	13
32	13
33	13
34	0
35	0
36	0
37	0
38	0
39	0

<colgroup><col><col></colgroup><tbody>
</tbody>

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!