Request for Excel formula for two conditions (Large to small, then small to large)

Hi Experts,


I need some help on the Excel formula.

1 I need a formula for Column D (yellow)
2 The first condition is Risk Exposure (Column B) should rank from largest to smallest.
3 The second condition is Management Preparedness (Column C) should rank from smallest to largest
4 The result should be as follows:

Ranking Risk Risk Exposure Management Prepardness Ranking
1 C1 3.2 1.6 C1
2 C4 3.2 1.6 C4
3 C5 2.5 3 C5
4 C2 2.3 5 C2
5 C3 1.4 3 C3