# From five columns with values (High, Medium High, Medium, Medium Low, Low), select largest



## Nathan_K (Jan 4, 2023)

Hello,

I have a spreadsheet that includes 5 columns, with each cell containing 5 possible words: High, Medium High, Medium, Medium Low or Low. For each row, I want to enter into a 6th column, the highest value/word between the 5 adjoining cells. In the examples below, within each row, the highest value between Columns A to E, is placed into Column F. Please kindly advise me what would be a good way to do this - what formula could I place in Column F that would make the comparison and place the result in Column F?

Thank you very much for any help you can lend.

I tried using IF(CountIF) but it popped out error message of too many arguments in the function. Maybe i wasn't doing it correctly on the formula.

Nathan


For example:
Col A Col B Col C Col D Col E Col F
High Medium High Medium-Low Low High
Low Medium Medium Medium-High Low Medium High
Low Low Low Medium Low Medium
Medium Low Medium Medium-High High High


----------



## StephenCrump (Jan 4, 2023)

Welcome to the Forum!

Do you mean like this?

ABCDEFGHI1"Highest"2HighMediumHighMedium LowLowHighHigh3LowMediumMediumMedium HighLowMediumMedium HighScale4LowLowLowMediumLowMediumMediumHigh5MediumLowMediumMedium HighHighHighHighMedium High6LowLowLowLowLowLowLowMedium7Medium Low8Low9Sheet1Cell FormulasRangeFormulaG2:G6G2=INDEX(Scale,MIN(MATCH(A2:F2,Scale,)))Named RangesNameRefers ToCellsScale=Sheet1!$I$4:$I$8G2:G6


----------



## Nathan_K (Jan 4, 2023)

StephenCrump said:


> Welcome to the Forum!
> 
> Do you mean like this?
> 
> ABCDEFGHI1"Highest"2HighMediumHighMedium LowLowHighHigh3LowMediumMediumMedium HighLowMediumMedium HighScale4LowLowLowMediumLowMediumMediumHigh5MediumLowMediumMedium HighHighHighHighMedium High6LowLowLowLowLowLowLowMedium7Medium Low8Low9Sheet1Cell FormulasRangeFormulaG2:G6G2=INDEX(Scale,MIN(MATCH(A2:F2,Scale,)))Named RangesNameRefers ToCellsScale=Sheet1!$I$4:$I$8G2:G6


Hi StephenCrump,

Thanks very much for the reply and suggestion. However, can i check if the results can be replicated using IF and Countif function? the reason why i asking for alternative method is because using Index function, i need to maintain a separate table to extract the correct value.

Thank you


----------



## Nathan_K (Jan 4, 2023)

Hi StephenCrump,

Your above solution works, however can enlighten me on how the formula works for this problem?

Thank you very much


----------



## kevin9999 (Jan 4, 2023)

Using @StephenCrump 's solution in post #2 - converting for use without a separate named range - does the following addition to Stephen's solution more closely meet your requirements?

Book1ABCDEFGH1 "Highest"2HighMediumHighMedium LowLowHighHigh3LowMediumMediumMedium HighLowMediumMedium High4LowLowLowMediumLowMediumMedium5MediumLowMediumMedium HighHighHighHigh6LowLowLowLowLowLowLow7Sheet1Cell FormulasRangeFormulaG2:G6G2=INDEX({"High","Medium High","Medium","Medium Low","Low"},MIN(MATCH(A2:F2,{"High","Medium High","Medium","Medium Low","Low"},)))


----------



## Nathan_K (Jan 4, 2023)

kevin9999 said:


> Using @StephenCrump 's solution in post #2 - converting for use without a separate named range - does the following addition to Stephen's solution more closely meet your requirements?
> 
> Book1ABCDEFGH1 "Highest"2HighMediumHighMedium LowLowHighHigh3LowMediumMediumMedium HighLowMediumMedium High4LowLowLowMediumLowMediumMedium5MediumLowMediumMedium HighHighHighHigh6LowLowLowLowLowLowLow7Sheet1Cell FormulasRangeFormulaG2:G6G2=INDEX({"High","Medium High","Medium","Medium Low","Low"},MIN(MATCH(A2:F2,{"High","Medium High","Medium","Medium Low","Low"},)))


Hi Kevin,

I tried copying your formula but it doesnt seem to work.

Appreciate your further advice.

Thanks much


----------



## kevin9999 (Jan 4, 2023)

Not sure what "doesnt seem to work" actually means.  Does it return an error (#Name!/#Value!/#Ref! etc.), does it return a blank, or an incorrect result?  Did you try copying the XL2BB sheet I posted to a blank worksheet, or did you copy the formula only and paste it into a cell in column F?  Is the issue the fact that you only wanted the "highest" value returned from columns A-E with the result put into column F?  If that is the case, try the alteration below instead:

Book1ABCDEFG1 "Highest"2HighMediumHighMedium LowLowHigh3LowMediumMediumMedium HighLowMedium High4LowLowLowMediumLowMedium5MediumLowMediumMedium HighHighHigh6LowLowLowLowLowLow7Sheet1Cell FormulasRangeFormulaF2:F6F2=INDEX({"High","Medium High","Medium","Medium Low","Low"},MIN(MATCH(A2:E2,{"High","Medium High","Medium","Medium Low","Low"},)))


----------



## Fluff (Jan 4, 2023)

Another option
Fluff.xlsmABCDEFG12HighMediumHighMedium LowLowHighHigh3LowMediumMediumMedium HighLowMediumMedium High4LowLowLowMediumLowMediumMedium5MediumLowMediumMedium HighHighHighHigh6LowLowLowLowLowLowLowDataCell FormulasRangeFormulaG2:G6G2=TAKE(SORTBY(A2:F2,MATCH(A2:F2,{"High","Medium High","Medium","Medium Low","Low"},0)),,1)


----------



## Nathan_K (Jan 4, 2023)

Fluff said:


> Another option
> Fluff.xlsmABCDEFG12HighMediumHighMedium LowLowHighHigh3LowMediumMediumMedium HighLowMediumMedium High4LowLowLowMediumLowMediumMedium5MediumLowMediumMedium HighHighHighHigh6LowLowLowLowLowLowLowDataCell FormulasRangeFormulaG2:G6G2=TAKE(SORTBY(A2:F2,MATCH(A2:F2,{"High","Medium High","Medium","Medium Low","Low"},0)),,1)


Hi Fluff, thank you very much! this solution works very well


----------



## Fluff (Jan 5, 2023)

Glad we could help & thanks for the feedback.


----------

