Automate criteria selection based on rank and score-CodePudding

I have 5 criteria in cell B2 to B6:

I want to return the criteria that is the highest risk (i.e. the lowest score), but this also needs to be based on rank. Meaning, criteria 1 is prioritized over criteria 2 if their scores are the same, but if criteria 2 has a lower score than 1, it is selected. This trend remains true throughout.

So overall, the criteria with the lowest score is selected, but if multiple criteria have the same score then the one with the highest rank is selected. See example below - criteria 3 is selected:

I want to automate this selection process as the scores will be constantly changing. Can I use formulas for this?

CodePudding user response：

If you have Excel 365 you can use this formula:

=LET(d,A2:C6,
rank,INDEX(d,,1), score,INDEX(d,,3),
sortByRankScore,SORTBY(d,score,1,rank,1),
INDEX(sortByRankScore,1,2))

Main idea: sort by score and rank - then return the first criteria from the result.

CodePudding user response：

You can use an "index match" formula to get the criteria with the lowest score.

=index(B2:B6;match(small(C2:C6;1);C2:C6;0))

However, this will result in "Criteria 3" just because it is above "Criteria 5" in column C. If this is always the case, you now have your answer. Since you have not indicated anything else, I won't go into more detail about solutions if that wasn't the case and if column C is much longer with multiple values of the same.

The column for the "rank" is unnecessary in this example as you have already written the criteras in such an order for it to be ranked correctly.