# Excel Formula to get Last 5 Non-Zero Values and Names from two columns respectively that have references

I have a range of data in four columns in excel as below. Column A, B are in Text format, columns C, D are generated using reference link. C(F) and D(F) are showing the formula bar values of C and D. Since Cells A10, A11, B10, B11 are empty C10, C11, D10, D11 show '0'.

I am trying obtain last 5 non zero values in such order as shown in Columns E and F, but based from Columns C and D only and not directly from columns A or B.

``````     A       B      C(F)    D(F)      C       D      E      F
1 Name     Val     Name    Val     Name     Val   Name     Val
2 abc01    101     =A2     =B2     abc01    101   abc04    104
3 abc02    102     =A3     =B3     abc02    102   abc05    105
4 abc03    103     =A4     =B4     abc03    103   abc06    106
5 abc04    104     =A5     =B5     abc04    104   abc07    107
6 abc05    105     =A6     =B6     abc05    105   abc08    108
7 abc06    106     =A7     =B7     abc06    106
8 abc07    107     =A8     =B8     abc07    107
9 abc08    108     =A9     =B9     abc08    108
10                  =A10    =B10      0       0
11                  =A11    =B11      0       0
``````

If a row of data is added to columns A and B, columns E and F should be reflecting as below.

``````     A       B      C(F)    D(F)      C       D      E      F
1 Name     Val     Name    Val     Name     Val   Name     Val
2 abc01    101     =A2     =B2     abc01    101   abc05    105
3 abc02    102     =A3     =B3     abc02    102   abc06    106
4 abc03    103     =A4     =B4     abc03    103   abc07    107
5 abc04    104     =A5     =B5     abc04    104   abc08    108
6 abc05    105     =A6     =B6     abc05    105   abc09    109
7 abc06    106     =A7     =B7     abc06    106
8 abc07    107     =A8     =B8     abc07    107
9 abc08    108     =A9     =B9     abc08    108
10 abc09    109     =A10    =B10    abc09    109
11                  =A11    =B11      0       0
``````

• `=INDEX(C:C, MATCH(0,C:C,0)-5, 1)` into `E2`
• `=INDEX(C:C, MATCH(0,C:C,0)-4, 1)` into `E3`
• `=INDEX(C:C, MATCH(0,C:C,0)-3, 1)` into `E4`
• `=INDEX(C:C, MATCH(0,C:C,0)-2, 1)` into `E5`
• `=INDEX(C:C, MATCH(0,C:C,0)-1, 1)` into `E6`
and then select them and drag/fill to the right, into `F2:F6`.  As I said at the beginning, this searches Column `C` for a 0, and then fetches the values from the five previous rows.
• May I make a suggestion? If you replace the "-5" in the first formula with `+ROW()-7`, then that one formula can be filled to the right and down. – Bandersnatch Oct 12 '17 at 8:52