Return the contents of the second last non empty cell within a column

[suprsnipes]

How can I return the contents of the second last non empty cell within a column? Is there a formula I can use?

Regards,
suprsnipes

 

[Rick Rothstein]

Not sure if this is the best way, but it does work.

=SUMPRODUCT(LARGE(ROW(A1:AX)*(A1:AX<>""),2))

Change the column letter as needed and change the two X's to a row number guaranteed to be larger than the largest row you ever expect to have data in.

 

[Rick Rothstein]

=SUMPRODUCT(LARGE(ROW(A1:AX)*(A1:AX<>""),2))

Change the column letter as needed and change the two X's to a row number guaranteed to be larger than the largest row you ever expect to have data in.

I received the following private message from Marcelo Branco (not sure why he didn't post this directly) which caught an omission with the formula posted. Here is the main part of his message...

Hi Rick,

I *think* you forgot the INDEX part

=INDEX(A1:AX ,SUMPRODUCT(LARGE(ROW(A1:AX)*(A1:AX<>""),2)))

Marcelo was absolutely correct... I concentrated on getting the row number correct that I forgot to plug it into an INDEX function call in order to get the requested contents of the cell. Note that there are now three X's to change.

Thanks Marcelo for catching that!

 

[Marcelo Branco]

Thanks Marcelo for catching that!

My pleasure.

M.