Find second last value in the column

[Kishan]

Hi,</SPAN></SPAN>

I need a formula that can find second last value in the column either it is number or alphabets.</SPAN></SPAN>

Resulting data...cell </SPAN></SPAN>C1

Book1
	A	B	C	D	E
1			X
2			P1
3			P1
4			P1
5			1
6			X
7			X
8			X
9			2
10			1
11			X
12			X
13			1
14
15
16
Sheet11

Thank you in advance</SPAN></SPAN>

Regards,</SPAN>
Kishan</SPAN>

 

[DanteAmor]

Hmm, index 0, eh? I am almost embarrassed to say that in all my time using that shortcut notation, it never occurred to me to try index 0 for the cell above the referenced cell (and I am not usually that rigid in my thinking).

Since there are no blanks in the OP's data, this regular formula should also work...

=INDEX(C:C,COUNTA(C2:C1000))

Thanks Rick for your comments, I really appreciate them.

 

[Kishan]

In that case, here is another VBA solution for you to consider...

Sub FindNextToLastInColumn()
  Dim Arr As Variant
  Arr = Range("C1", Cells(Rows.Count, "C").End(xlUp))
  Range("C1") = Arr(UBound(Arr) - 1, 1)
End Sub

By the way, alansidman's approach can be reduced to a one-liner program...

Sub FindX()
  Range("C1") = Cells(Range("C" & Rows.Count).End(xlUp).Row - 1, "C")
End Sub

Hi Rick, thank you so much both worked fine</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan </SPAN></SPAN>

 

[Kishan]

Another VBA solution:

Sub penultimate()
    [C1] = Range("C" & Rows.Count).End(xlUp)(0)
End Sub

Hi DanteAmor, thank you so much worked fine</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan </SPAN></SPAN>

 

[Kishan]

How about a "regular" formula


=LOOKUP(1,1/(C5:C1000<>(LOOKUP(1,1/(C5:C1000<>""),C5:C1000))),C5:C1000)

Hi DanteAmor, thank you this formula return 0</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>

 

[Kishan]

Hmm, index 0, eh? I am almost embarrassed to say that in all my time using that shortcut notation, it never occurred to me to try index 0 for the cell above the referenced cell (and I am not usually that rigid in my thinking).




Since there are no blanks in the OP's data, this regular formula should also work...

=INDEX(C:C,COUNTA(C2:C1000))

Hi Rick, thanks your regular formula return X it is fine!</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan </SPAN></SPAN>

 

[DanteAmor]

Hi DanteAmor, thank you so much worked fine

Kind Regards,
Kishan

I'm glad to help you. Thanks for the feedback.

 

[DanteAmor]

Hi DanteAmor, thank you this formula return 0

Kind Regards,
Kishan

Yore right, There would be a more complex formula, vote for Rick's formula

=LOOKUP(2,1/((C5:C1000<>(LOOKUP(2,1/(C5:C1000<>""),C5:C1000)) ) * (C5:C1000<>"")),C5:C1000)

 

[Kishan]

Yore right, There would be a more complex formula, vote for Rick's formula

=LOOKUP(2,1/((C5:C1000<>(LOOKUP(2,1/(C5:C1000<>""),C5:C1000)) ) * (C5:C1000<>"")),C5:C1000)

Hi DanteAmor, thanks, yes this one worked fine!</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan </SPAN></SPAN>

 

[DanteAmor]

With pleasure