Using a match and lookup formula, but wanting to reference the rightmost column ticked?

i have a little matrix that i use to reflect what staff have been to which training. the dates for the training are referenced in cells C6 - AI 6, along the top of the table. the staff names are down the side from b9 to b29. i have data validation in the cells to identify when someone has attended the training, and put an A in the box.

this is referenced on their individual sheet tabs, so the first member of staff on the list has the following in the box which identifies when the last time they attended training was.

=INDEX(Supervision!6:6, MATCH("a",Supervision!9:9,0))

this is great if they only ever attend one training session, but as the dates are set for throughout the year, i would like my formula to pick up the rightmost A in that particular row, which would correspond with the last time that they received training. I hope that that makes sense.

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Try this ...

=LOOKUP(2,1/(Supervision!9:9="a"),Supervision!6:6)

THANKS!!! thats perfect.