Unique Ranking with Multiple Criteria

Hi Experts,

I am trying to do ranking on both ascending and descending order with multiple criteria

Below is the Data Set :
Data Set.JPG
B3:E40
*Data set will have some blank rows

The blue shaded cells are the result i would like to have :
result.JPG

When I change the ranking Criteria in Cell H5 and H6, the ranking can automatically change

Can you please advice the formula in cell H9:H13 and H16:H20? Thanks!

Attach please the excel file as well !
Unique Rank with multiple criteria.xlsx

Regards,
Chocobo

Hi Glenn,

Yes, there will be tied value, if ham4 and ham5 both be 17, the ranking should be not the same, i.e., even it both rank in 4, result should be show rank in 4 for ham4 and rank in 5 for ham5.

Regards,
Chocobo

Originally Posted by Glenn Kennedy

One way:

H9:
=INDEX(D:D,AGGREGATE(15,6,ROW($B$4:$B$33)/(($B$4:$B$33=$H$5)*($C$4:$C$33=$H$6)),ROWS($1:1)))

I9:
IFERROR(1/(1/INDEX($E$4:$E$33,MATCH(1,INDEX(($B$4:$B$33=$H$5)*($C$4:$C$33=$H$6)*($D$4:$D$33=$H9),0),0))),"")

Can there be tied values? i.e. could ham4 and Ham 5 both be 17... or whatever?

Hi Glenn,

Yes, there will be tied value, if ham4 and ham5 both be 17, the ranking should be not the same, i.e., even it both rank in 4, result should be show rank in 4 for ham4 and rank in 5 for ham5.

Regards,
Chocobo