# Excel Formula To Get Last Non Zero or Not Blank Value in Column And Return Value

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))))
``````

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:

``````=XLOOKUP("*",A2:A10,A2:A10,"N/A",2,-1)
``````

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

• A basic implementation without the infinities:

``````=XLOOKUP(TRUE,A2:A10<>"",A2:A10,"N/A",0,-1)
``````

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

``````=MAX(IF(A2:A10="",0,ROW(A:A)))
``````

or

``````=XLOOKUP(TRUE,A2:A10<>"",ROW(A2:A10)-ROW(A2)+1,0,0,-1)
``````

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:

`=LOOKUP(2,1/(A:A<>0),A:A)`

`=LOOKUP(2,1/(A2:A10<>0),A2:A10)`

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

`=LOOKUP(1,0/(0+A:A>0),A:A)`

N.B.

Second formula is an optional.

Adjust cell references in the formula as needed.

LOOKUP is no longer recommended. To use XLOOKUP:

``````=XLOOKUP(2,1/(A2:A10<>0),A2:A10,"N/A",-1,-1)
``````