Ranking based on criteria

Hello,

I would like to ask your support regarding a ranking issue.
I want to identify the TOP1, TOP2 and TOP3 Value (in column E) based on Designation but I cannot figure out, please see the details in the attached file.

The expected results are in column G.

Thank you in advance the feedback!

Try this in E9:

=CHOOSE(SUM(IF(($C$9:$C$21=C9)*($D$9:$D$21>=D9),1/COUNTIFS($C$9:$C$21,C9,$D$9:$D$21,$D$9:$D$21))),
"TOP1","TOP2","TOP3","Not in the TOP3")

Array formula, enter with Ctrl+Shift+Enter.

Hmm, typically you'd consider ranking in following manner.

When there is tie for 1st spot, next rank skips to 3rd. Since 1st and 2nd are occupied by the ties.

That calculation can be done using...
=COUNTIFS($C$9:$C$21,C9,$D$9:$D$21,">"&D9)+1

However, you are counting ties as occupying single spot (1st) and gives top 3 to 4 separate values for LH2.
Is this intended?

Thank you very much, the solutions are working well!