2nd and 3rd last number in a column - how to get it?

[mpenkala]

Hi there,
I currently have a table from columns V3 to AM1000. The cells contain
numbers varying from 0 to 50. I have a formula in place that calculates the
last number in the column:

=LOOKUP(2,1/(V$3:V$1000<>""),V$3:V$1000)

what I'm looking for is 2 formulas,
- one that will return the 2nd last number in the column into cell AP3
- one that will return the 3rd last number in the column into cell AQ3

Ex.

ColumnV looks like the following:

0
1
0
1
2
3
0
0

Now, my forumla gives my 0 as the last number. I would like AP3 to show the
2nd last number (0) and AQ3 to show (3) as it's the 3rd last number.

Thanks!
Matt

 

[Gord Dibben]

In AP3 enter =OFFSET(INDEX($V3:$V1000,MATCH(99^99,$V3:$V1000)),-1,0)

In AQ3 enter =OFFSET(INDEX($V3:$V1000,MATCH(99^99,$V3:$V1000)),-2,0)


Gord Dibben MS Excel MVP

 

[T. Valko]

As long as there are no empty/blank/text cells within the range:

2nd to last:

=INDEX(V$3:V$1000,COUNT(V$3:V$1000)-1)

3rd to last:

=INDEX(V$3:V$1000,COUNT(V$3:V$1000)-2)

For the last number:

=LOOKUP(1E100,V$3:V$1000)

 

[mpenkala]

Thanks Gord - works great!

Matt

In AP3 enter =OFFSET(INDEX($V3:$V1000,MATCH(99^99,$V3:$V1000)),-1,0)

In AQ3 enter =OFFSET(INDEX($V3:$V1000,MATCH(99^99,$V3:$V1000)),-2,0)


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Thanks for the feedback.

See also Biff's shorter suggestion if you have no blanks.


Gord