In this article:

Google Sheets Switch Function: The Ultimate Guide for 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

FAQs

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:

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

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

Automate everything you track in spreadsheets with Lido
Learn more

Automate manual tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Trigger
Action
Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
ADDCALENDAREVENT
Creates a one- or multiple-day calendar event with optional attendees
ADDHUBSPOT
Adds an object to Hubspot
ADDSLACKCHANNEL
Create a Slack channel, and optionally add a topic or members
CALLURL
Makes any HTTP request
CREATEGOOGLEDOC
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
CREATEPDF
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
FETCH
Makes any HTTP request and returns the response
INSERTROWS
Inserts given array below defined values in given worksheet
SENDGMAIL
Sends an email using your Google account
SENDOUTLOOK
Sends an email using your Microsoft Outlook account
SENDSLACK
Sends a Slack message
SENDSMS
Sends an SMS message using your Twilio account
UPDATECELL
Updates cells with given values
UPDATEHUBSPOT
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started