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

[Nathan_K]

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]

Welcome to the Forum!

Do you mean like this?

	A	B	C	D	E	F	G	H	I
1							"Highest"
2	High	Medium	High	Medium Low	Low	High	High
3	Low	Medium	Medium	Medium High	Low	Medium	Medium High		Scale
4	Low	Low	Low	Medium	Low	Medium	Medium		High
5	Medium	Low	Medium	Medium High	High	High	High		Medium High
6	Low	Low	Low	Low	Low	Low	Low		Medium
7									Medium Low
8									Low
9
Sheet1

Cell Formulas
Range	Formula
G2:G6	G2	=INDEX(Scale,MIN(MATCH(A2:F2,Scale,)))

Named Ranges
Name	Refers To	Cells
Scale	=Sheet1!$I$4:$I$8	G2:G6

 

[Nathan_K]

Welcome to the Forum!

Do you mean like this?

	A	B	C	D	E	F	G	H	I
1							"Highest"
2	High	Medium	High	Medium Low	Low	High	High
3	Low	Medium	Medium	Medium High	Low	Medium	Medium High		Scale
4	Low	Low	Low	Medium	Low	Medium	Medium		High
5	Medium	Low	Medium	Medium High	High	High	High		Medium High
6	Low	Low	Low	Low	Low	Low	Low		Medium
7									Medium Low
8									Low
9
Sheet1

Cell Formulas
Range	Formula
G2:G6	G2	=INDEX(Scale,MIN(MATCH(A2:F2,Scale,)))

Named Ranges
Name	Refers To	Cells
Scale	=Sheet1!$I$4:$I$8	G2: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]

Hi StephenCrump,

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

Thank you very much

 

[kevin9999]

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?

Book1
	A	B	C	D	E	F	G	H
1							"Highest"
2	High	Medium	High	Medium Low	Low	High	High
3	Low	Medium	Medium	Medium High	Low	Medium	Medium High
4	Low	Low	Low	Medium	Low	Medium	Medium
5	Medium	Low	Medium	Medium High	High	High	High
6	Low	Low	Low	Low	Low	Low	Low
7
Sheet1

Cell Formulas
Range	Formula
G2:G6	G2	=INDEX({"High","Medium High","Medium","Medium Low","Low"},MIN(MATCH(A2:F2,{"High","Medium High","Medium","Medium Low","Low"},)))

 

[Nathan_K]

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?

Book1
	A	B	C	D	E	F	G	H
1							"Highest"
2	High	Medium	High	Medium Low	Low	High	High
3	Low	Medium	Medium	Medium High	Low	Medium	Medium High
4	Low	Low	Low	Medium	Low	Medium	Medium
5	Medium	Low	Medium	Medium High	High	High	High
6	Low	Low	Low	Low	Low	Low	Low
7
Sheet1

Cell Formulas
Range	Formula
G2:G6	G2	=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]

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:

Book1
	A	B	C	D	E	F	G
1						"Highest"
2	High	Medium	High	Medium Low	Low	High
3	Low	Medium	Medium	Medium High	Low	Medium High
4	Low	Low	Low	Medium	Low	Medium
5	Medium	Low	Medium	Medium High	High	High
6	Low	Low	Low	Low	Low	Low
7
Sheet1

Cell Formulas
Range	Formula
F2:F6	F2	=INDEX({"High","Medium High","Medium","Medium Low","Low"},MIN(MATCH(A2:E2,{"High","Medium High","Medium","Medium Low","Low"},)))

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G
1
2	High	Medium	High	Medium Low	Low	High	High
3	Low	Medium	Medium	Medium High	Low	Medium	Medium High
4	Low	Low	Low	Medium	Low	Medium	Medium
5	Medium	Low	Medium	Medium High	High	High	High
6	Low	Low	Low	Low	Low	Low	Low
Data

Cell Formulas
Range	Formula
G2:G6	G2	=TAKE(SORTBY(A2:F2,MATCH(A2:F2,{"High","Medium High","Medium","Medium Low","Low"},0)),,1)

 

[Nathan_K]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G
1
2	High	Medium	High	Medium Low	Low	High	High
3	Low	Medium	Medium	Medium High	Low	Medium	Medium High
4	Low	Low	Low	Medium	Low	Medium	Medium
5	Medium	Low	Medium	Medium High	High	High	High
6	Low	Low	Low	Low	Low	Low	Low
Data

Cell Formulas
Range	Formula
G2:G6	G2	=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]

Glad we could help & thanks for the feedback.