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
- Type “=SWITCH” or navigate to the “Insert” tab (or “Functions” icon) → “Function” → “Logical” → “SWITCH”.
- Input “expression”, “case”, and “value” by manual input or cell reference.
- Enter “case” and “value” until you incorporate all corresponding values.
- Finally, include “default” if needed.
- Press the “Enter” key.
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.
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.
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.
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.
