I already saw a few related questions on here about finding the last item but my question is a bit different because I'm trying to find the last not blank or non zero value in a column and either return that value or return 0 if there is nothing there. I have written a formula that works for me except for the very first row in the column and returns an error because it can't find a value. I tried to change the formula to return a 0 if it doesn't find anything but apparently wound up making the problem worse. Here is my current attempt at a formula:

=IF(B12=0,0,IF(LOOKUP(2,1/($B$10:B11<>0),$B$10:B11)=0, 0, B12 - IF(ISBLANK(LOOKUP(2,1/($B$10:B11<>0),$B$10:B11)), 0, LOOKUP(2,1/($B$10:B11<>0),$B$10:B11))))

enter image description here

3 Answers 3


If there's anybody else who finds division by zero/FALSE to be aesthetically displeasing then here are some alternatives:

• An efficient XLOOKUP method if you're dealing with strings, not numbers:


This searches the range from the bottom up and stops when it finds a non-empty string.

• A basic implementation without the infinities:


• If you're looking for a position rather than the value:




In these cases a return value of zero means an empty range.


I would like to suggest few formula works in different situations:

  • Formula returns last non-blank, non-zero value in column or range:



  • Another is formula returns last non blank non zero positive value only in column or range:



Second formula is an optional.

Adjust cell references in the formula as needed.


LOOKUP is no longer recommended. To use XLOOKUP:


You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .