1

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

2

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.

1

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.

1

LOOKUP is no longer recommended. To use XLOOKUP:

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

You must log in to answer this question.

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