The SWITCH function in Google Sheets is an easy way to create a switch statement in your spreadsheet.
This function works similar to nesting multiple IF statements together, however, with SWITCH, it is much easier.
In this tutorial, I will show you how to use the SWITCH function in Google Sheets
The SWITCH function will take an expression and test it against a list of cases. It will then return the value corresponding to the first matching case.
This function allows you to create a switch statement in Google Sheets.
The syntax of this function is:
SWITCH(expression, case1, value1, [case2, value2, …], [default])
- expression – any value or expression. This is what will be tested against each case for a match
- case1 – the first case that the expression will be checked against
- value1 – this is the value that will be returned if case1 matches the expression
- case2 – this is an optional argument. You can continue to test additional cases if case1 is not a match
- value2 – this is an optional argument. This is where you place the values to return if your additional cases are a match
- default – this is an optional argument. You can specify a value here that will be returned if there are no matching cases
Using the SWITCH Function
Now let’s take a look at how to use the function.
In this example, I will set up the SWITCH function with the data below
The ratings in column D will be my “expression” arguments that I will check against the values in column A (the “case” arguments). If the case matches the expression, I will then return the value in column B.
Here are the steps to set up this formula:
1. First select the cell where you want the formula to calculate and type “= SWITCH”. Then press TAB to begin entering into the formula
2. The first argument of this function is the expression. This is the value that will be tested against each case. In my example, this is in cell D2, so that is what I have entered into the function
3. Next is the case1 argument. This is the value that the expression will be checked against. In my example this is in cell A2, so that is what I have entered into the function
4. If your expression argument matches your case1 argument, the next value is the value that will be returned. In my example, I will return the value in cell B2 if cell D2 matches cell A2. So B2 is what I place as my next parameter in the formula.
5. If you have additional cases you want to test against and values you want to return, you can place additional arguments here. I will also test my expression against cell A3, and if it is a match, I will return the value in cell B3. If that doesn’t match, I will then also test my expression against cell A4, and if it matches, return the value in cell B4. So those are the next arguments I have placed in the function
6. When you are done entering your arguments, press the Enter key to calculate your formula. You can see in this example that my formula returns the value in cell B2, because D2 is a match for cell A2
7. If you have additional expressions that you want to check, you can copy and paste your formula down. Notice how the expression in cell D4 returns an #N/A error. This is because there is no matching cases
8. If you want to set a custom value to be returned when there are no matching cases, you add that as the last argument of your function. Here I have set the last argument to return “Not a valid rating” if there are no matching cases. That is what is returned for the expression in cell D5.
The SWITCH function is a very efficient way to test a value against multiple cases for matches. As you can see in the example above, you can also return a custom value if there are no matching cases.
If you are familiar with other functions, you may realize that this is kind of similar to using an IF function. However, SWITCH cannot use logical expressions, unfortunately.
So if you need to use a logical expression like “greater than” or “less than” you may have to use the IF function.