# Formula that displays the last non-zero value cell

I have a report that is able to pull/display values based on yesterday's values.

Is there a way I can modify the formula to ignore cells that contain zeros "0s" and to continue to reference/pull the LAST cell that contains a numeric value?

Current formula:

``````=INDEX(SM!40:40,COUNTA(SM!A40:AG40))
``````

Screenshot: You can find the last numeric value in row 1 (excluding `0`s, blanks and text) with:

``````=index(filter(1:1,1:1<>0),count(filter(1:1,1:1<>0)))
``````
• This is great! Is it possible return the column reference of the last numeric (non zero) number in a row? I can do it in Excel, but the formula is not working in Sheets. – steve klein Jun 6 '18 at 1:23
• Yes true... thx! – steve klein Jun 6 '18 at 1:25

In my test, changing the row reference to a column reference (column U in this case), the above formula returns the NEXT-to-last non-zero value. I was able to get the LAST value as follows, but I don't know whether this formula would work generally or there is something particular about my spreadsheet that requires the "+1," so I hesitate to recommend this as a general solution:

``````=index(filter(U:U,U:U<>0),count(filter(U:U,U:U<>0))+1)
``````
• Actually, I must amend my previous comment. Now that more values have been added to my column U, the formula returns a totally wrong result. I should mention that the non-zero values in my column are mostly negative numbers. Maybe that confounds the formula. – Steve Jan 16 at 4:24
• On another site, I found a formula that seems to work correctly: =ArrayFormula(iferror(lookup(1,U:U/U:U,U:U))) where my figures are created by a formula in each cell of Column U; the formula produces a result of 0 where the data isn't supplied yet. (This is a spreadsheet updated daily with the previous day's results.) Source: infoinspired.com/google-docs/spreadsheet/… – Steve Jan 16 at 4:37