Firstly, SWITCH evaluates a logical expression and matches with one of the cases available. It then returns the value defined in that case block. If there is no matching case, it returns the default value. This process is explained in the
Syntax
=SWITCH(logical_expression, case1, value1, [case2, value2]…, default)
logical_expression– a value, expression or reference to a cell containing a value or expression that returns a value. This is what the function will test each case against to see if they match.
case1 – the value with which the SWITCH function compares the value from the expression.
value1 – the value that the function returns as an output if the value from the expression matches with that of case1.
default – the function returns this value as output if none of the cases match with that of the value from the expression. The default input is optional.
case1– the value with which the SWITCH function compares the value derived fromlogical_expression.
value1– the value that the function returns as an output if the value derived from thelogical_expressionmatches with that of thecase1.
default– the funcion returns this value as output if none of the cases match with that of the value derived fromlogical_expression>.
How to use the SWITCH function
Example 1:
Let’s start with the standard version of the function, and take a look at how it works. In the example below, I’ve typed: =Switch(A2,1,”Alex”,2,”Bob”,3,”Charlie”,”None”).
In the above example, the expression is just ‘2’. It is compared with all three of the cases available, and it matches with the second case. So the function returns the value ‘Bob’ in the cell.
Example 2:
What happens when none of the cases match with the expression? Let’s experiment:
The expression ‘5’ matches with none of the available cases, as a result, the function returns the default value ‘None’.
What does the SWITCH function return if you don’t define any default value? It produces an error, as shown in the screenshot below.
This is why it’s always helpful to define a default value!
Example 3:
Here’s a more complex example.
I’ve planned out my wardrobe for the week, with every weekday coinciding with a dress color.
In the third column, I’ve entered various days of the week, and in the fourth column I’m using the SWITCH function to match the day of the week with the correct color by typing =Switch(C2,A2,B2,A3,B3,A4,B4,A5,B5,A6,B6,”Not a valid weekday”).
The function here is long, but you can easily break it down into three components.
The first part is the cell reference from the “What day is it?” column – this is what the function is looking for.
After this, there are a series of case and value combinations – each one declares that if the reference cell is equal to a certain day of the week, then the function should output the corresponding color.
Finally, there is a default value at the end, which tells the function what to output if none of the cases match the input value.
Should I use SWITCH or IF function?
To illustrate the benefits of the SWITCH function compared to the IF function, I’ve done the same example as before, but using IF.
Take a look at how much more complicated the formula is:
IF function works in a similar way to SWITCH, but unlike SWITCH, it can be used with logical expressions such as “smaller than” or “greater than”.
If you wish to perform logical tests on your data, use IF. But if you’re looking for direct matches to conditions in your dataset, SWITCH can be a helpful option to avoid using a series of long and complex nested IF functions.
Learn more about How to use the IF function in Google Sheets and How to use the CHOOSE formula in Google Sheets.
Editor’s note: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.
