how to find the 2nd last value of a column

[Lamb Chop]

I have a column (A:A) and some cells are empty. In B1, I would like to get
the value of the 2nd last value in column A.

e.g. example 1

A1 = 434
A2 = 4343
A3= <empty>
A4 = 11
A5 = <empty>
A6= <empty>
A7 = 343
A8 = 43

In B1, I want it to be 343 (A7)

example 2

A1 = 434
A2 = 4343
A3= <empty>
A4 = 11
A5 = <empty>
A6= <empty>
A7 = <empty>
A8 = 43

In B1, I want it to be 11 (A4)


Thanks.

Man

 

[Bob Phillips]

=INDEX(A:A,LARGE(IF((A1:A100<>"")*(A1:A100<>""),ROW(A1:A100)),2))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Ron Rosenfeld]

I have a column (A:A) and some cells are empty. In B1, I would like to get
the value of the 2nd last value in column A.

e.g. example 1

A1 = 434
A2 = 4343
A3= <empty>
A4 = 11
A5 = <empty>
A6= <empty>
A7 = 343
A8 = 43

In B1, I want it to be 343 (A7)

example 2

A1 = 434
A2 = 4343
A3= <empty>
A4 = 11
A5 = <empty>
A6= <empty>
A7 = <empty>
A8 = 43

In B1, I want it to be 11 (A4)


Thanks.

Man

=INDEX(A1:A65535,LARGE(IF(LEN(A1:A65535)>0,ROW(A1:A65535)),N))

entered as an array formula. Hold down <ctrl><shift> while hitting <enter>.
Excel will place braces {...} around the formula.

For "N", substitute which "from the last" entry you want to return. e.g. in
your question it would be 2:

=INDEX(A1:A65535,LARGE(IF(LEN(A1:A65535)>0,ROW(A1:A65535)),2))

If you are using Excel 2007, you may reference the entire column:

=INDEX(A:A,LARGE(IF(LEN(A:A)>0,ROW(A:A)),2))
--ron