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?

 

[gaz_chops]

Try

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

 

[arthurbr]

Have a look at http://www.xldynamic.com/source/xld.LastValue.html and try using an explicit range

 

[DavidWF]

gaz_chops - that worked perfectly!

arthubr - the first formula under #001 worked perfectly, as did the first formula under each of #002 and #003 .

Brilliant! - four different ways of solving the problem and I hadn't previously thought of any of them

arthurbr, that web site is very interesting, I've book-marked it for future reference. I currently colour-code cells on another worksheet, depending on the result of the particular row, and I've had it in the back of my mind that one day I'm going to want to count and/or sort the rows based on the coloured cells. That web site will be a great guide for me when I finally decide to attack that issue.

My grateful thanks to both of you for solving my immediate LOOKUP problem.

 

[DavidWF]

Deleted due to double-posting

 

[Phuoc]

On both sheets col AB is a mix of numeric values and blank cells

Try:

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

 

[DavidWF]

Thanks Phuoc, that also works, so that's now five different ways to resolve my problem. You guys rock!!

 

[DavidWF]

Sorry, but the gremlins have struck!

I'm currently implementing the above recommendations on two worksheets. On one worksheet the output from each of the five alternative formulae gives the expected result:
https://1drv.ms/u/s!ArANX3rGwxC6bG3iLJScJV_8iYs

On the other worksheet I get a zero value output, regardless of which of the five formulae I use:
https://1drv.ms/u/s!ArANX3rGwxC6bQEP1T0laA2HhRI

The formulae I've used are:

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

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

{=INDEX(AB:AB,MAX(IF(ISNUMBER(AB1:AB65535)*(AB1:AB65535<>""),ROW(AB1:AB65535))))}

=LOOKUP(9.99999999999999E307,AB:AB)

=INDEX(AB:AB,MATCH(9.99999999999999E307,AB:AB))

Any thoughts on why each formula should work on one sheet but not work on another virtually identical sheet will be very much appreciated.

 

[Phuoc]

=LOOKUP(2,1/($AB:$AB<>""),$AB:$Ab)

 

[DavidWF]

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.