Lookup the 2nd to last cell used in a column

S

SteveC

How do I modify this:

=LOOKUP(2,1/(A1:A10<>""),A1:A10)

which looks up the last value in a column so it instead retrieves the second
to last value in a column, third to last value in a column, fourth to last,
etc.

offset this value -1 somehow? or -2, -3 etc... not sure how to use OFFSET

thanks very much!
 
S

SteveC

=LOOKUP(MAX(Data!B3:B500)-1,Data!B3:B500)
=LOOKUP(MAX(Data!B3:B500)-2,Data!B3:B500)
=LOOKUP(MAX(Data!B3:B500)-3,Data!B3:B500)
etc
 
B

Billy Liddel

SteveC said:
How do I modify this:

=LOOKUP(2,1/(A1:A10<>""),A1:A10)

which looks up the last value in a column so it instead retrieves the second
to last value in a column, third to last value in a column, fourth to last,
etc.

offset this value -1 somehow? or -2, -3 etc... not sure how to use OFFSET
Click to expand...

If you want to reverse the list copy this into row 1 and copy down:
=INDEX($A$1:$A$10,(COUNTA($A$1:$A$10)-ROW()+1))

if you want to just pick from the list and enter the number in B2 to be
picked 4 willl give you the fourth entry from the bootom of the list:

=INDEX($A$1:$A$10,(COUNTA($A$1:$A$10)-B2+1))

Regards
Peter
 
Top