Display next to last results

  • Thread starter Thread starter Luke
  • Start date Start date
L

Luke

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
 
you can use

=indirect("Sheet2!A"&counta(Sheet2!a:a)-1)

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Luke" escreveu:
 
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
 
=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)
 
Marcello, the formula returned a blank cell. I think because it is looking
for text and my data is numeric. However I do have blank cells in the column.
Any thoughts?
Luke
 
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
 
If you want non-array entered, then try this one

=INDEX(A:A,LARGE(INDEX((A1:A100<>"")*ROW(A1:A100),),2))
 
Yeah, that is a lot better than mine (I seem to have been "off" all day
today). Thanks.

Rick
 
Maybe this:
=indirect("Sheet2!A"&count(Sheet2!a:a)-1)

Just a guess...

Regards,
Ryan---
 
Back
Top