Add a Value to a text and give me an score-CodePudding

I'm trying to create a formula depending on the values of a dropdown in Google Sheets, so Basically I have Low, Medium and High their values are 1, 2 and 3 so I would like to multiplicate this numbers in two columns to do have an score this is my list:

Rate    Rate two    Total score
Medium  Low 
High    High    
Low     Medium

How can I solve it with and if?

I have this formula it works but I would like to know a better option

=(SWITCH(B2, "High", 1, "Medium", 2, "Low", 3,, 0))*(SWITCH(C2, "High", 1, "Medium", 2, "Low", 3,, 0))

CodePudding user response：

I would add a small config table mapping the value to each rate. Then you can use INDEX/MATCH like this:

=INDEX($F$2:$F$4;MATCH(A2;$E$2:$E$4;0))*INDEX($F$2:$F$4;MATCH(B2;$E$2:$E$4;0))

CodePudding user response：

try:

=INDEX(QUERY(VLOOKUP(A3:B6, {"High", 1; "Medium", 2; "Low", 3}, 2, ), 
 "select Col1*Col2 label Col1*Col2''", ))