[Lamb Chop[_2_]]

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<""),RO W(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)

"Lamb Chop" wrote in message
...
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

[Ron Rosenfeld]

On Thu, 12 Jun 2008 16:48:22 +1000, "Lamb Chop" wrote:

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