How to get the last match of a string over a column and print the subsequent column's value in-CodePudding

I am forced to use excel for work reasons and I am a novice with that...so forgive me for the stupid question. I have an excel sheet like that:

I would like to have in cell B53 (the green coloured one) the last satus registered in column F for SN0006 --> status "READY". So, steps are:

Find all the occurrences that matches cell A53 content, id est SN0006, in the column B ("SN")
Take the last occurrence (at the n-1 row). Dunno if it could help but consider that column "EVENT ID" is ordered in an ascending manner so that the last occurrence will have a higher index
Print in cell B53 the content of the column "STATUS" respect to the last SN0006 occurrence row.

Until now I was able to print the STATUS of the first SN0006 occurrence "BRING UP" in that way:

=INDEX(F39:F51;MATCH(A53;B39:B51;0))

Thank you in advance for any help!

CodePudding user response：

This is an array formula which will work in Excel 2007 and later

=INDEX(F39:F51,MAX((A53=B39:B51)*ROW(B39:B51))-38)

CodePudding user response：

Finally I also find a way but I didn't understand how it works...

=INDEX(F39:F51;MATCH(2;1/(B39:B51=A53)))