In this article:

May 8, 2024

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

****

Get Google Sheets productivity and automation tips delivered straight to your inbox

We'll email you 1-3 times a week — and never share your information.

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->