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

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

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

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

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
--
Bernard V Liengme
Microsoft Excel MVP
http://www.yqcomputer.com/
remove caps from email

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

On 29 Dec, 20:02, "Bernard Liengme" < XXXX@XXXXX.COM >

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

Cheers Jamie

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

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)

--
Biff
Microsoft Excel MVP