How to Use The Google Sheets SWITCH Function [Easy Guide]

The Google Sheets SWITCH function is a helpful tool for changing how a cell behaves based on the value in another cell.

It’s incredibly useful in data interpretation and presentation.

To understand the SWITCH function, think of it as a function that can check multiple IF conditions. For example, you can check whether is a value is A or B or C, and based on what it is, you can return a corresponding value.

The benefit of using the SWITCH function is that it’s easier to read and understand, as compared with a nested IF function which can soon become quite complex as the number of conditions to check increases.

However, the SWITCH function has one significant limitation: the conditional case statements do not work with operators like “less than” and “greater than.” Therefore you need to keep in mind that SWITCH is only viable when you’re working with exact matches.

The following processes break down the SWITCH function syntax and demonstrate how to use it in real-life situations.

This Article Covers:

Syntax of the Google Sheets SWITCH Function

The SWITCH function in Google Sheets has three main parts:

The Expression: This defines the values the function will test. This can be a cell reference such as “B2”
Cases: The google sheets case statement checks the value against the expression for an exact match. An example case is a numerical value: 0.
Values: If the expression and related case are an exact match, this is what the function returns in the cell. An example return value is a text “No.”

The Google Sheet SWITCH function syntax with two cases looks like this:

=SWITCH(expression, case1, value1, case2, value2)

Note that each attribute within the function is separated by a comma. When filled in with values, a SWITCH function will look something like this:

=SWITCH(B2,0,"No",1,"Yes")

In this example, the function interprets the contents of cell B2. If the cell value is 0, it returns “No” while if the cell value is 1, it returns “Yes.”

Example 1 – Using the SWITCH Function With Two Conditions

Let’s use a SWITCH function in Google Sheets real-world example.

The following dataset records the mailing list subscription status of 10 customers pulled from the server, but the server designates subscription status with a 0 for “No” and a 1 for “Yes.”

While this dataset makes sense if you know what you’re looking for, it won’t make sense to a person who is unfamiliar with how the server stores subscription status.

So we’ll use a simple SWITCH function to make the data human-readable.

Build your SWITCH function based on the topmost row. In the example, we are looking at row 2.
- The expression is the cell we’re looking to analyze. In the example, it is “B2.”
- Determine the case/value pairs. In this example, the first case/value pair is 0 and “No” while the second case/value pair is 1 and “Yes.”
- Combine those values into the function. The example uses the following function:
```
=SWITCH(B2,0,"No",1,"Yes").
```
Insert the SWITCH function into the topmost cell in the return column.
Drag the bottom right square down the column to fill in the rest of the data.

The example dataset makes more sense to human interpretation now thanks to the SWITCH function.

Example 2 – Using SWITCH function with One Condition and a Fallback (Default)

If you don’t account for all possible Google Sheets case matches in a SWITCH function, it will return an error.

Fortunately, you can add the optional “default” value (like a fall-back value) to the end of the statement to return a value when there isn’t a matching case.

The SWITCH function with a single case and a default setting syntax looks like this:

=SWITCH(expression, case1, value1, default)

If we’re using the SWITCH function to check the pass/fail status of students based on grades, we can configure it to return “Fail” when it identifies an “F” case and “Pass” for all other cases.

The function looks like this when filled out:

=SWITCH(B2,"F","Fail","Pass")

Our example data shows five students with different grades, we want to return either a “Pass” or a “Fail” value in the “Pass/Fail” column.

The following steps demonstrate how to apply a SWITCH statement with a default value.

Build your SWITCH function based on the topmost row. In the example, we are looking at row 2.
- The expression is the cell we’re looking to analyze. In the example,it is “B2.”
- Determine the case/value pairs. In our example, we have a single case/value pair with a case of “F” and a value of “Fail.”
- Determine the default return. In our example, it’s “Pass.”
- Combine those values into the function. The example uses the function:
```
=SWITCH(B2,"F","Fail","Pass")
```
Insert the SWITCH function into the topmost cell in the return column.
Drag the bottom right square down the column to fill in the rest of the data.

The SWITCH formula now fills column C with a “Fail” for every “F” grade and a “Pass” for every other grade under the default condition.

Example 3 – Using a Google Sheets SWITCH Statement with Two Conditions and a Fallback (Default)

Let’s take one more look at using the SWITCH Google Sheets function with multiple case/value pairs and a default setting.

Our example data looks at the shift lengths of five employees on a given day.

We want to fill the “Shift Type” column with “Full” for a full eight-hour shift, “Off” for zero-hours designating no shift, and “Partial” for any other value.

The following steps detail how to use a SWITCH function with all features enabled:

Build your SWITCH function based on the topmost row. In the example, we are looking at row 2.
- The expression is the cell we’re looking to analyze. In the example, it is “B2.”
- Determine the case/value pairs. In our example, the first case/value pair is 8 and “Full” while the second case/value pair is 0 and “No.”
- Determine the default return. In our example, it’s “Partial.”
- Combine those values into the function. The example uses the function:
```
=SWITCH(B2,8,"Full",0,"Off","Partial")
```
Insert the SWITCH function into the topmost cell in the return column.
Drag the bottom right square down the column to fill in the rest of the data.

The SWITCH function now populates the column with the conditional value responses.

Note: Google Sheets doesn’t put a limit on how many case/value pairs you use in a SWITCH function. This tutorial uses only two cases at most for ease-of-explanation.

SWITCH Function vs IFS Function

Apart from the SWITCH function, there is another function that you can use as a substitute for the nested if formulas – the IFS function.

IFS function, as the name suggests, can accommodate multiple if conditions. It’s a relatively new function that makes nested if formulas are easier to read (just like the SWITCH function).

While the SWITCH function can be used when you want to compare an exact value with the expression, within the IFS function you can also use operators such as greater than or less than.

Personally, I prefer using the IFS formula as it can work with numbers as well as text values (including the cases where I need to compare using operators such as greater than or less than). The SWITCH function is more suited in conditions where you need to check for an exact match, which is most useful when your working with text data.

Google Sheets SWITCH Function FAQ

Is There a SWITCH Function in Google Sheets?

Yes, there is a SWITCH function in Google Sheets. It works similarly to the IF and IFS functions.

How Do You Switch Column A and B in Google Sheets?

You do not use the SWITCH function to switch columns. All you have to do is:

Select the entire column you’d like to move by clicking the column header A for example
Click, hold and drag it to the other side of the opposing column, B for example

This tutorial covered the foundation for using the Google Sheets SWITCH function.

I hope you found it useful!

Other Google Sheets tutorials you may find useful: