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:




2 Answers 2


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



  • 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. Jun 6, 2018 at 1:23
  • 1
    Yes true... thx! Jun 6, 2018 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:

  • 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, 2020 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, 2020 at 4:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.