Return last entry in column that matches criteria

Hi thanks for your response - I've attached a workbook to give you a better visual on what I am trying to achieve. As you can see from the example, the username is showing as "ABC1" but that username has been suspended... The correct username should be "ABC2"

Thanks