Find the last, 2nd last and 3rd last data in a column

[jamiejgash]

I have data listed in several colums and need to create a formula to
detail the last (most recent) 2nd last and 3rd last piece of data in
the column.

I have used the following formula to display the last but cant edit
this or create another formula to get the 2nd and 3rd last pieces of
data.

=LOOKUP(2,1/(A:A<>0),A:A)

Thanks in advance

Jamie

 

[Bernard Liengme]

If there are no empty cells in the range:
last cell: =INDEX(A:A,COUNTA(A:A))
penultimate: =INDEX(A:A,COUNTA(A:A)-1)
semi-penultimate: =INDEX(A:A,COUNTA(A:A)-2)
best wishes

 

[jamiejgash]

If there are no empty cells in the range:lastcell: =INDEX(A:A,COUNTA(A:A))
penultimate: =INDEX(A:A,COUNTA(A:A)-1)
semi-penultimate: =INDEX(A:A,COUNTA(A:A)-2)
best wishes
--
Bernard V Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme
remove caps from email








- Show quoted text -

My coloumns do have blanks, is there any function that can ignore
blank/empty cells.

Cheers Jamie

 

[T. Valko]

Try this array formula** :

Entered in a cell then copied down this will return in order: last, 2nd to
last, 3rd to last items. No error checking!

With data in the range A1:A15 and the array formula** entered in C1 and
copied down:

=INDEX(A$1:A$15,LARGE(IF(A$1:A$15<>"",ROW(A$1:A$15)-MIN(ROW(A$1:A$15))+1),ROWS(C$1:C1)))

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