Find the Last Value in a Column of Array Formulas

[DavidWF]

I currently use

=LOOKUP(2,1/(1-ISBLANK($AB:$AB)),$AB:$AB)

to find the last value in col AB where I have manually entered the cell values.

I just tried to use the same formula on another worksheet but on this sheet every value in col AB is the result of an array formula. The =LOOKUP formula is returning a blank, even though col AB has thousands of values in it. On both sheets col AB is a mix of numeric values and blank cells, the difference being that on the sheet where I manually enter data the blank cells are truly blank, whereas on the second sheet the blank cells contain an underlying formula that hasn't returned a numeric value.

I'm guessing that the blank output is because of the array formula. How should I amend the =LOOKUP formula to work with the array data?

 

[Phuoc]

=LOOKUP(10^35,$AB:$AB/$AB:$Ab)

 

[Aladin Akyurek]

Thanks Phuoc - the issue is now solved.

I tried your formula but got the same result - a zero.

I was puzzled why so many different formulae were failing to give the expected result when those same formulae worked fine in other locations so I started to probe a bit further. I eventually found a block of data/formulae several thousands of rows down the sheet, with multiple cells displaying a zero. I have absolutely no idea how this block came to be down there, but deleting the 6000 rows immediately below my active data area on the sheet solved the issue.

Your formula worked fine once those rows had been deleted.

If you are after the last numeric value, it's just this what you need:

=LOOKUP(9.99999999999999E+307,AB:AB)

If you want to exclude 0's the target reference might have as a result...

=LOOKUP(9.99999999999999E+307,1/AB:AB,AB:AB)

 

[DavidWF]

Hi Aladin
Sorry for the late response, I've been away for a few days.

I had to recreate the problem to test your formulae, by putting the zero values back into the worksheet again. And - your formula worked perfectly!!

All the previous answers worked fine provided there wasn't the block of zero values lurking at the bottom of the worksheet, but your last formula did indeed return the last true numeric value and value and excluded the 0's that were further down. Brilliant!

Thanks again to everyone who has contributed and provided me with a collection of alternative solutions.

 

[Aladin Akyurek]

You are welcome.