How can I locate the second to last value in a range?

[A shink]

I am trying to locate the last value in a column and then the second to last,
then third to last and so on to be placed in a seperate section of my
spreadsheet.

I am using the following formula to locate the last cell which is:
Lookup(2,1/(A3:A145<>"")),A3:A145)

Where do I go from here?

Thanks for the help

 

[Duke Carey]

=offset(a1, [your formula from below]-1,0,1,1) will give you the last value
in col A.

Change the -1 to -2, -3 , etc for the other cells

 

[A shink]

Duke:

That addition to the formula actually gives me the first filled row of the
column not the last. Anyother ideas?

thanks

=offset(a1, [your formula from below]-1,0,1,1) will give you the last value
in col A.

Change the -1 to -2, -3 , etc for the other cells

I am trying to locate the last value in a column and then the second to last,
then third to last and so on to be placed in a seperate section of my
spreadsheet.

I am using the following formula to locate the last cell which is:
Lookup(2,1/(A3:A145<>"")),A3:A145)

Where do I go from here?

Thanks for the help

 

[Duke Carey]

Try this formula for the LAST value. As before, adjust the -1 to get the
previous cell, etc

=OFFSET(A1,MATCH(9.99999999999999E+307,A:A)-1,0,1,1)

Duke:

That addition to the formula actually gives me the first filled row of the
column not the last. Anyother ideas?

thanks

=offset(a1, [your formula from below]-1,0,1,1) will give you the last value
in col A.

Change the -1 to -2, -3 , etc for the other cells

I am trying to locate the last value in a column and then the second to last,
then third to last and so on to be placed in a seperate section of my
spreadsheet.

I am using the following formula to locate the last cell which is:
Lookup(2,1/(A3:A145<>"")),A3:A145)

Where do I go from here?

Thanks for the help

 

[A shink]

Thank you for all the help!!!!

Try this formula for the LAST value. As before, adjust the -1 to get the
previous cell, etc

=OFFSET(A1,MATCH(9.99999999999999E+307,A:A)-1,0,1,1)

Duke:

That addition to the formula actually gives me the first filled row of the
column not the last. Anyother ideas?

thanks

=offset(a1, [your formula from below]-1,0,1,1) will give you the last value
in col A.

Change the -1 to -2, -3 , etc for the other cells



:

I am trying to locate the last value in a column and then the second to last,
then third to last and so on to be placed in a seperate section of my
spreadsheet.

I am using the following formula to locate the last cell which is:
Lookup(2,1/(A3:A145<>"")),A3:A145)

Where do I go from here?

Thanks for the help

 

[Aladin Akyurek]

I am trying to locate the last value in a column and then the second to last,
then third to last and so on to be placed in a seperate section of my
spreadsheet.

I am using the following formula to locate the last cell which is:
Lookup(2,1/(A3:A145<>"")),A3:A145)

Where do I go from here?

Thanks for the help

Last numeric value:

=LOOKUP(9.99999999999999E+307,A3:A145)

Second to the last numeric value:

=LOOKUP(9.99999999999999E+307,A3:INDEX(A3:A145,MATCH(9.99999999999999E+307,A3:A145)-1))

If you meant to "locate" any value (last entry of any type), a different
type of formula is required.