Formula to return string value based on ranked categories-CodePudding

This question might have a very simple answer. I have 3 values (i.e. Low, High and Extreme) that correspond with 5 categories (Low, Moderate, High, Very High and Extreme), ranked from low to high.

I want to find a formula that returns the highest-ranked string value (i.e. Extreme), without having to add extra columns for category or value scores.

Hope someone can help.

CodePudding user response：

Here’s one approach, which does require O365 because of the FILTER() function. This would be simpler if we knew for sure how many values were in your values column. Since I don’t know that, I ended up adding a little complexity with the FILTER() function. This prevents the MATCH() function from returning #N/As for blank values, which in turn would break the MAX() function:

=INDEX({"Low","Medium","High","Very High","Extreme"},MAX(MATCH(FILTER(A1:A5,A1:A5<>""), {"Low","Medium","High","Very High","Extreme"},0)))

This can be further reduced by the LET() function:

=LET(
categories, {"Low","Medium","High","Very High","Extreme"},
INDEX(categories,MAX(MATCH(FILTER(A1:A5,A1:A5<>""),categories,0)))
)

Also, if this is a sheet that will get a lot of re-use, you could make the categories a named value using the Excel Name Manager. If you did that the formula would simply be:

=INDEX(categories,MAX(MATCH(FILTER(A1:A5,A1:A5<>""),categories,0)))

CodePudding user response：

Try:

Formula in C2:

=@SORTBY(B2:B4,MATCH(B2:B4,A2:A6,0),-1)

Note that the order in 'B' does not matter as long the order in 'A' is correct.