Finding 2nd last entry
 

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

Finding 2nd last entry
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"dartanion" wrote in message
...
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

Finding 2nd last entry
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"dartanion" wrote in message
...
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

Finding 2nd last entry
 

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


Replace @mailinator.com with @tiscali.co.uk


"Don Guillett" wrote in message
...
or
=INDEX(A:A,MATCH(99999999999,A:A)-1)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"dartanion" wrote in message
...
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