# Finding a Zero Value in one column and the corresponding value in 5th column from previous row

I have a table that has 5 columns. The rows after some point will have a value of zero. I need to get the value of the previous row of column 5.
For example; here after 4th row, all the value become zero. I then need the value 89 to appear in some designated Cell outside this table.

``````Year    Amt1    Amt 2   Amt 3    Amt 5
2012         5       6      17      48
2013        15       0      72      83
2014        25      63       0      12
2015         0      56      75      89
0         0       0       0       0
0         0       0       0       0
0         0       0       0       0
``````
-
In the title of your question you write about the 3rd column and in the text "the value of the previous row of column 5"? – duDE Aug 13 '13 at 8:32
Fixed the typo error – Dheer Aug 13 '13 at 8:39
Can there be zeroes in Column 5 before 89? Or in Year perhaps? – Jerry Aug 13 '13 at 8:53
There will be no Zeros in either column 1[Year] or in Column 5 [Amt 5]. However there can be zeros in other columns Amt1, 2, 3. So essentially I need the last non zero value in column 5. – Dheer Aug 13 '13 at 9:01

A formula that would suit what you're looking for would be this:

``````=OFFSET(E2,MATCH(0,E:E,0)-3,0)
``````

`E2` is the first value in column 5 (48)

`OFFSET` with the cell at the beginning of the column and moves down by `MATCH(0,E:E,0)-3`.

`MATCH(0,E:E,0)` will return the row in which it can find the first occurrence of `0` (The parameters are: look for `0` into column `E:E` and use `0` to mean exact match).

The `-3` is to account for the header, the first cell and move up one cell because `MATCH` will return the position of `0`, not the one of the cell just above it.

The last parameter in `OFFSET` is by how many columns the range should move, and since we're already in column E, there's no movement in this respect.

EDIT: Since the column `Year` are perfect zeroes (the column 5 contains very small numbers rounded to 1), you can use this instead:

``````=OFFSET(E2,MATCH(0,A:A,0)-3,0)
``````

## Understanding OFFSET

Say you use the formula:

``````=OFFSET(A1, 1, 1)
``````

`OFFSET` will return the cell which is found 1 row below and 1 column after the cell `A1`, meaning cell `B2`.

Similarly, `=OFFSET(A1, 3, 1)` will give the cell `B4`.

There are actually more options you can have in `OFFSET`, namely the height and the width of the resulting range.

``````=OFFSET(A1, 1, 1, 2, 3)
``````

`OFFSET` will return the cell which is found 1 row below and 1 column after the cell `A1`, meaning cell `B2`. Then, the height of the result will be 2, and the width 3, meaning it would return the range `B2:D3` (if you select this range with your mouse, you see it'll be 2 cells high and 3 cells wide). However, since this is an array, you will get an `#N/A` with this formula. You can still carry out operations with this array, such as `VLOOKUP` or `INDEX`, `SUM`, etc.

-
Thanks. I had figured out the MATCH portion, but was not able to figure out the Ofset function. The value in Column 5 although shows zero [computed field] is really some small decimal ... 5 zeros after decimal :). So changed the MATCH to -1 and its giving me the desired result. If we can find something based on Year column that is zero it would be perfect. – Dheer Aug 13 '13 at 9:23
Sorry figured out the offset much better now =OFFSET(E2,MATCH(0,A2:A10,0)-3,0) – Dheer Aug 13 '13 at 9:28
@Dheer I included a more indepth description about `OFFSET` in my answer, just in case ^^. Also, if you start at `A2` in your match, you need to remove `2` instead of `3`! – Jerry Aug 13 '13 at 9:38
Yes Indeed, got a hold of the function. Thanks. – Dheer Aug 13 '13 at 9:43