In this article:

Google Sheets Switch Function: The Ultimate Guide for 2024

May 8, 2024

When to Use Google Sheets SWITCH Function?

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.

How to Use Google Sheets SWITCH Function

1. Identify the Cases and their Corresponding Results

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

google sheets switch

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.

2. Use the formula =SWITCH(expression, case1, result1, case2, result2, …,others_result)

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.

switch google sheets

The formula can get long, but this is more compact than if we use the IF function.

google sheets case statement

3. Press Enter

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.

google sheets switch statement autofill

We are now done! It’s that simple.

Google sheets switch statement


Can SWITCH Function be Used with a Range of Values?

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:


Where we define ranges with their minimum and maximum values. For example, we have the following values:

Input values of different numbers

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

ranges of numbers in switch function google sheets

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
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.
Get your copy of our free Google Sheets automation guide!
  • 27 pages of Google Sheets tips and tricks to save time
  • Covers pivot tables and other advanced topics
  • 100% free

Work less, automate more!

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