Replace/Substitute/Exchange words to numbers

[jarrgonn]

I was trying to get creative with the SWITCH function but I might have done it wrong.

I have a large file where 1 column contains 4 specific words only but in random order - MINIMAL, LOW, MODERATE, HIGH. I need to 'assign' a number/value to each of those words:
→ 1 = Minimal
→ 2 = Low
→ 3 = Moderate
→ 4 = High

I attached a sample file.

Thanks.

[FDibbins]

If I understand you correctly, try this...
L5=VLOOKUP(K5,$H$5:$I$8,2,0)
copied down as needed.

If that wasnt what you wanted, plz provide some sample answers.

[shukla.ankur281190]

Or can be use sumifs too as the result is in numeric

=SUMIFS($I$5:$I$8,$H$5:$H$8,K5)

[FDibbins]

yuppers or just SUMIF...
=SUMIF($H$5:$H$8,K5,$I$5:$I$8)

[jarrgonn]

The columns that has the header COLUMN 1 & 2 are the desired results. The file I have has up to Column AS and the column that has MINIMAL, LOW, MODERATE, HIGH is only 1 of them.

So basically, these dont show on the file that I'm working on:
Column 1 Column 2
Minimal 1
Low 2
Moderate 3
High 4

I tried =SWITCH(K5, Minimal, "1", Low, "2", Moderate, "3", High, "4", 0) but it didnt work. So, I guess it only works if the value is a number...

[shukla.ankur281190]

Yes Ford!!! I have practiced sumifs rather than sumif as sumifs is state forward Sumrange, CriteriaRange, Criteria but I must ignore this practice as if there is only one condition we must go with sumif only it is because if there is sumif then there must be some reason,

[shukla.ankur281190]

May be you have entered wrongly text into switch function as text must be mention in " "

Try
=SWITCH(K5, "Minimal", 1, "Low", 2, "Moderate",3, "High",4)

Have look at here https://exceljet.net/excel-functions...witch-function

[jarrgonn]

Oh gosh... how could I have missed that! Thank you!!!

[FDibbins]

Then I suggest that you create a small table somewhere - like the 1 you showed, and then use vlookup...or 1 of the other suggestions...to get what you want. Why make things more complicated than they need to be (not all versions of excel have the SWITCH function - I dont)?

[shukla.ankur281190]

Yes agree with Ford!!! I don't have too switch function in my excel version.