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))
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))