Formula to find the 2nd to last value in column

[smide]

Hello.

This is my example data set.


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]H[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]....[/TD]
[TD="align: center"]....[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

With formula (in cell C1) =lookup(2,1/(A1:A10=3),B1:B10) I could find the last value in column B for a number 3 and result in cell C1 is letter G.

I need a formula to find a second to last value for number 3 (in this example) and result in cell C1 should be letter C.

 

[Fluff]

How about

Excel 2013/2016
	P	Q	R
1	1	A	C
2	2	B
3	3	C
4	4	D
5	1	E
6	2	F
7	3	G
8	4	H
Sheet1

Cell Formulas
Range	Formula
R1	{=INDEX(Q1:Q8,LARGE(IF(P1:P8=3,ROW(P1:P8)),2))}
Press CTRL+SHIFT+ENTER to enter array formulas.