Arrow left
Back to guides
Google Sheets Formulas

How to Use SWITCH Function in Google Sheets

In this article, you will learn how to utilize the SWITCH formula in Google Sheets. The SWITCH function is beneficial when you want to show a corresponding value to another value (e.g., converting numerical grade to alphabetical grade).

How to use the SWITCH formula in Google Sheets

  1. Type “=SWITCH” or navigate to the “Insert” tab (or “Functions” icon) → “Function”“Logical”“SWITCH”.
  2. Input “expression”, “case”, and “value” by manual input or cell reference.
  3. Enter “case” and “value” until you incorporate all corresponding values.
  4. Finally, include “default” if needed.
  5. Press the “Enter” key.
How to insert the SWITCH function in Google Sheets

The general syntax is as follows:

Expression: This is a value tested by this formula. You should input this value by a cell reference, applying this function to many values.

Case1: If a tested value matched this value, “case1”, the formula returns a value defined as “value1”. If a tested value is not the same as this value, the function keeps searching other cases (if specified) for the tested value.

Value1: This is a value shown in the cell when a tested value equals “case1”.

Case2: Same as “case1”.

Value2: Same as “value1”. This value should be paired with “case2”.

Default (optional): This is optional. If this default value is set, it is returned by the formula when the formula can’t find any match between a tested value and each case. Otherwise, the function returns “#N/A”.

Note: You need to input pairs of “case” and “value” until you include all pairs. The values mentioned above can be text, number, date, etc.

Assume you are a group manager and use a numerical grade to evaluate your group members (“Score”). However, you need to switch the grading system from the numerical one to an alphabetical one (“Grade”) due to the change in your company’s policy. You defined the following relationships between scores and grades: “1”=”E”, “2”=”D”, “3”=”C”, “4”=”B”, and “5”=”A, and organize a table showing their correspondences. See the following examples.

Example 1 - manual Input version

This example shows what the formula looks like if you use manual input. 

How to use the SWITCH formula in Google Sheets with examples

The arguments in the SWITCH function in cell D4 look as follows:

Expression: C4

Case1: “1”

Value1: “E”

Case2: “2”

Value2: “D”

Case3: “3”...

Default (optional): Not defined in this formula

The formula in cell D4 tests the value in cell C4, which is “5”, and returns “A” because the tested value matches “case5”, which is “5”, and thus “Value5”, which is “A” is shown in the cell. However, as Amelia and Lucas have scores that are not defined in the formula, the formulas return “#N/A”. 

Example 2 - cell reference version

The following example shows the same results, but all formulas are input by cell references, which is highly recommended in terms of accuracy and convenience.

How to use the SWITCH function by cell reference in Google Sheets

The arguments in the SWITCH function in cell D16 look as follows:

Expression: C16

Case1: $K$16

Value1: $L$16

Case2: $K$17

Value2: $L$17

Case3: $K$18…

Default (optional): Not defined in this formula

In this example, the table describing relationships between alphabets and numbers is leveraged by being referred to in the formula.

Example 3 - cell reference version with a default value

The last example shows what happens if the default value is included in the SWITCH formula. You determine that assuming a score is equal to or more than 1, you give an “A+” grade to scores that are not tied to specific alphabets, such as “7” and “8”, Amelia’s and Lucas’ scores, respectively. 

How to use the SWITCH function with "default" defined in Google Sheets

The grades for Amelia and Lucas, shown as “#N/A” for the last two examples, now appear as “A+” correctly. This is how the “default” argument works. The “default” argument should be included in the formula as the last input, without being paired with any value. Otherwise, the formula recognizes them as a pair of “case” and “value”.

How do you switch two columns in Google Sheets?

Check this article to learn how to switch two columns in Google Sheets.

How do I swap cells in Google Sheets?

Unfortunately, you can‘t swap two cells in Google Sheets, though you can do it in Excel. Consider switching two columns instead.

How do you switch rows and columns in Google Sheets?

Check this article to learn how to transpose rows and columns by Spacial Paste or the TRANSPOSE function in Google Sheets.

Learn how to do this step-by-step in the video below 👇

Automate financial reporting with LiveFlow

Want to eliminate manual updates of your Excel & Google Sheets models?

Yes, show me how

Need help?

Our team is here to help you any time between 9am and 10pm EST.
Check Icon
Email us at:

Liked this article? Then you'll love the ones below