How to Use SWITCH function in Google Sheets?

The SWITCH function in Google Sheets 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.

Syntax of the SWITCH function in Google Sheets

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 function checks the value of a case 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 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.”

Using SWITCH function with Two Conditions

Let’s put this to use in a 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.”

Dataset for SWITCH function

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.

  1. 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").
  2. Insert the SWITCH function into the topmost cell in the return column.Using the SWITCH function
  3. Drag the bottom right square down the column to fill in the rest of the data.Draf the SWITCH function to the entire column

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

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

If you don’t account for all possible 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.

Student's data for SWITCH function

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

  1. 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")
  2. Insert the SWITCH function into the topmost cell in the return column.SWITCH functiont to get pass fail
  3. Drag the bottom right square down the column to fill in the rest of the data.Apply the formula to the entire column

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.

Using SWITCH function with Two Conditions and a Fallback (Default)

Let’s take one more look at using the SWITCH 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.

Hours worked data for switch function

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

  1. 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")
  2. Insert the SWITCH function into the topmost cell in the return column.Enter the SWITCH formula in the top most cell
  3. Drag the bottom right square down the column to fill in the rest of the data.Copy paste the formula to the entire column

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.

This tutorial covers the foundation for using SWITCH statements in Google Sheets.

I hope you found it useful!

Other Google Sheets tutorials you may find useful:

Leave a Comment