Grabbing the Second-to-Last matching value in a column

[smide]

Hello.


In columns A and B there is a product no. and product list in my sheet.


The last matching value I have found with formula ... =lookup(2,1/(B2:B10=C2),A2:A10)
How can I find Second to Last matching value?


example.

[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"]Product no.[/TD]
[TD="align: center"]List[/TD]
[TD="align: center"]Matching text in cell C2[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]apple[/TD]
[TD="align: center"]cherry[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]orange[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]cherry[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]cherry[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]orange[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]apple[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]cherry[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]apple[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So, above formula returns value for last cherry occurrence and it's a 7, but I also need a formula to return second to last cherry occurrence, in this case it's a number 4.
Result/formula should be placed in cell C3.

 

[Aladin Akyurek]

In C3 just enter:

=COUNTIFS($A$2:$A$9,"<>",$B$2:$B$9,C$2)

In C4 control+shift+enter, not just enter, and copy down:

=IF(ROWS(C$4:C4)>$C$3,"",INDEX($A$2:$A$9,SMALL(IF($B$2:$B$9=C$2,IF(1-($A$2:$A$9)=""),ROW($A$2:$A$9)-ROW($A$2)+1)),ROWS(C$4:C4))))