SWITCH Function: An Alternative to IF Function in Google Sheets
Learn to use this useful and valuable function which will give you greater control in altering and exchanging values when working in Google Sheets.
Review of IF Function
The IF function in Google Sheets is used for analyzing an input, applying a certain condition to it, and then selecting an output depending on whether it satisfies the condition or not. It takes the following syntax:
For example, if we want to apply the following conditions to the value of cell A5:
If A5>3, display “Sufficient”; else, display “Insufficient”
We will write the function as follows:
The IF function can also be nested. It means that you can insert the IF function inside another IF function. This is useful if you plan to apply a series of conditions into the data. To extend our example above, let’s say we add the following condition:
If there are more conditions to apply, you can nest more IF functions.
If what you have is a set of “values” that serve as a switch instead of conditions, then using the IF function can be too much. For this purpose, you can use the SWITCH function.
The SWITCH Function, Using Numbers as Input
The SWITCH function is a convenient way of applying conditional formatting when a small set of values serve as “choices” in which conditional formatting is then applied.
If a function you use outputs one of three possible values, let’s say 1, 2, and 3, and you need to convert them to their corresponding meanings AAA, BBB, and CCC, respectively, you can use the SWITCH function as follows:
The expression can either be a formula or a cell reference.
For example, let’s say we have a case where, after applying data analysis, we get one of the following choices:
5 - Excellent
4 - Satisfactory
3 - Acceptable
2 - Average
1 - Needs Improvement
If, for example, we have the data on B2, we can use the switch function as follows:
In data munging, you need to add functions that will indicate whether the input is already a valid input or not. We can do so using the SWITCH function.
The SWITCH function can count whether there is a one-to-one correspondence between the specified input and its corresponding output. In our previous example, note that there is a one-to-one correspondence between the specified input and its corresponding output:
If there is a space entry that has no corresponding pair, the SWITCH function can identify the last entry as the “default”. The “default” serves as the output when the actual input is not specified in the function. If a “default” is not specified, the function will display a #N/A error:
The “default” output can simply be added at the end of the SWITCH function: