# Get last non zero value from a contiguous range of a column



## hsandeep (Dec 21, 2022)

In my worksheet, I have 2 contiguous ranges: A1:A10000 which generates numerical values in all cells. The generation of values in A1:A10000 can be divided in 2 parts
Part1: It generates numerical values >0 and is *contiguous*
Part2: It generates 0 (zero)
Example: If A1:A33*3* >0, then A33*4*:A10000 =0 (zer0)

Output cell
F2=Fetch value from the cell from A1:A10000 which has generated *LAST non zero value
Formula required for F2*

Help provided would be highly appreciated. I am using Excel 2010.


----------



## aRandomHelper (Dec 21, 2022)

Maybe this in F2:

```
=INDEX(A:A,MATCH(0,A:A,0)-1)
```


----------



## hsandeep (Dec 22, 2022)

Your formula works quite well.  Thanks a lot aRandomHelper


----------



## Scott Huish (Dec 22, 2022)

If there could me multiple zeros in the range, then perhaps this:
=LOOKUP(2,1/(A:A<>0),A:A)


----------



## hsandeep (Dec 22, 2022)

Scott Huish said:


> If there could me multiple zeros in the range, then perhaps this:
> =LOOKUP(2,1/(A:A<>0),A:A)


There *WOULD BE* multiple zeros in the Range A1:A10000.  What I want to learn is: the *difference in results* of the 2 formula, please Scott
Formula by aRandomHelper

```
=INDEX(A:A,MATCH(0,A:A,0)-1)
```
Formula by you

```
=LOOKUP(2,1/(A:A<>0),A:A)
```


----------

