Find the second to the last occurence of a value in a column with multiple criteria

[queenamidala]

Hi! I need your help. I am trying to create a formula in finding the second to the last occurrence of a value in a column with multiple criteria. I attached the file for the sample table. There should be 5 columns. A is for Date, B is for UserNumber and C for CurrentLevel. In column D I need to get the previous level of the specified username. I used this formula:

=INDEX(C$2:C4,SUMPRODUCT(MAX((B$2:B4=B5)*ROW(B$2:B4)))-ROW(C$2:C4)+1)

And in column E I need to get the previous level of the specified username from the previous month. This is the formula that I am trying to use but looks so wrong!

=INDEX(C$2:C4,SUMPRODUCT(A2:A4=MONTH(A5-1)*MAX((B$2:B4=B5)*ROW(B$2:B4)))-ROW(C$2:C4)+1)

Please help!

Thanks a lot!

[Jacc]

I couldn't get it to work with SUMPRODUCT so I used an array formula (enter with Ctrl + Shift + Enter).
I also simplified your old formula a bit.


For some reason MONTH would not work inside the SUMPRODUCT, weird. Maybe someone else has some knowledge of this?