In SQL, if we want to test an expression in several cases, we use the CASE expression. It allows us to define not just one case but several ones in a compact format. Its equivalent in Google Sheets is the SWITCH function.
The SWITCH function in Google Sheets is best used when you want to check if an expression has an exact match with the list of cases you have.
You need to first identify the cases you need to test an expression, with their corresponding results.
For our example, we have a series of item codes with their corresponding ratings in letters ranging from A (highest) to E (lowest).
We want to convert these letter ratings to their corresponding words:
A: Excellent
B: Satisfactory
C: Acceptable
D: Average
E: Needs Improvement
These are the corresponding case-result pairs that we will program in the SWITCH formula. Since we want to consider the possibility of having an input that does not fit in the given cases above, we add the following result:
Others: Invalid Input
The SWITCH function has its own way of adding this result, which will be explained in the next step.
The SWITCH formula has the following syntax:
=SWITCH(expression, case1, result1, case2, result2, …,others_result)
Where the expression can be the specific expression or the address to the cell containing the expression to compare. Each case follows their corresponding result, separated by a comma.
If you want to include a catch-all result to cover all the other cases, simply add the others_result at the end of the formula. For our example, the formula becomes:
=SWITCH(B2,”A”,”Excellent”,”B”,”Satisfactory”,”C”,”Acceptable”,”D”,”Average”,”E”,”Needs Improvement”,”Invalid Input”)
Where B2 contains the expression to compare against the set of cases.
The formula can get long, but this is more compact than if we use the IF function.
Once you have added the formula to the cell, press Enter. Google Sheets will automatically make a suggestion to auto fill all the other cells within the column. You can click the check mark if the autofill suggestion is good for you.
We are now done! It’s that simple.
The SWITCH function is defined to be used to check if an expression exactly matches a set of cases. Thus, it is best used to compare strings with a set of possible values. It is not designed to compare a cell value with a set of ranges of values. However, there is a workaround, using the AND function inside the SWITCH function:
=SWITCH(TRUE,AND(range1_min,range1_max),range1_result,AND(range2_min,range2_max),range2_result,...,out_of_range_result)
Where we define ranges with their minimum and maximum values. For example, we have the following values:
And we have the following ranges with their corresponding results:
1-10: Lvl 1
11-20: Lvl 2
21-30: Lvl 3
The formula becomes
=SWITCH(TRUE,AND(A2>0,A2<11),"Lvl 1",AND(A2>10,A2<21),"Lvl 2",AND(A2>20,A2<31),"Lvl 3")
The result is
As you can see, this is a workaround to using the SWITCH function for setting ranges. Alternatively, you can simply use the IFS function as it is designed to compare values with given ranges. The formula using IFS function is
=IFS(A2<11,"Lvl 1",A2<21,"Lvl 2",A2<31,"Lvl 3")
Note this is shorter than the formula using SWITCH because you can define the conditions in the IFS function so that you don’t need to define both the maximum and the minimum for each range:
=SWITCH(TRUE,AND(A2>0,A2<11),"Lvl 1",AND(A2>10,A2<21),"Lvl 2",AND(A2>20,A2<31),"Lvl 3")
