Finding 2nd last entry

[dartanion]

I am using the formula =LOOKUP(2,1/(1-isblank(b10:b65536)),b10:b65536) to
return the last entry in column b, and this works perfectly well, however, I
need to also use the 2nd last entry, anyone any ideas

 

[Don Guillett]

One way. Also, use less than 65536
=INDEX(A:A,LOOKUP(2,1/(1-ISBLANK(A10:A65536)),A10:A65536)-1,0)

 

[Don Guillett]

or
=INDEX(A:A,MATCH(99999999999,A:A)-1)

 

[Sandy Mann]

The OP's use of ISBLANK() makes me think that there are holes in the data.
If one of the holes is the cell above the last cell with data in it then
your formula will return 0.

The best that I can come up with is:

=INDIRECT("A"&LARGE(((A1:A1000<>"")*ROW(1:1000)),2))

Which is not only an array formula but is also valatile.

I am sure that someone can come up with one that is neither.

To the OP the above formula should be entered with Ctrl + Shift + Enter not
just Enter

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk