If you want non-array entered, then try this one

=INDEX(A:A,LARGE(INDEX((A1:A100<"")*ROW(A1:A100), ),2))


"Rick Rothstein (MVP - VB)" wrote:

Yes, that is true if there is a blank space in the row immediately in front
of the last data item. Good catch. Here is a modification to what I posted
that should work and is non-array entered...

=OFFSET(A1,MATCH(LOOKUP(2,1/(INDIRECT("A1:A"&(MATCH(LOOKUP(2,1/(A1:A65535<""),A:A),A:A,0)-1))<""),A:A),A:A,0)-1,0)

Yeah, it is longer than your array-entered offering, but it seems to work.

Rick


"T. Valko" wrote in message
...
=OFFSET(A1,MATCH(LOOKUP(2,1/(A1:A65535<""),A:A),A:A,0)-2,0)

That will return incorrect results.

Assuming there will be at least 2 non-empty cells.

Array entered** :

=INDEX(A:A,LARGE(IF(A1:A100<"",ROW(A1:A100)),2))

Since this is an array formula the inner range references can not be
entire columns (unless you're using Excel 2007).

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
If your data in Column A has no gaps in it, this formula can be used...

=OFFSET(A1,COUNTA(A:A)-2,0)

If there are (or can be) gaps in your data, then this formula can be
used...

=OFFSET(A1,MATCH(LOOKUP(2,1/(A1:A65535<""),A:A),A:A,0)-2,0)

Rick


"Luke" wrote in message
...
This formula works great to find the last entry in a col.
=LOOKUP(99^99,Sheet1!A:A)
Is there a way to get the formula to show the 2nd to the last entry?

Thank you for all you do,
Luke