Lookup the 2nd to last cell used in a column
 

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!

Lookup the 2nd to last cell used in a column
 

=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

"SteveC" wrote:

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!

Lookup the 2nd to last cell used in a column
 

"SteveC" wrote:

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

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

Lookup the 2nd to last cell used in a column
 

Assuming no blank cells in between the data

=INDEX(A:A,COUNT(A:A)-1)


"SteveC" wrote:

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!

Lookup the 2nd to last cell used in a column
 

Another way...

=INDEX(A1:A7,ROWS(A1:A7)-1)


"SteveC" wrote:

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!