The SWITCH Function is one of Excel’s logical functions that can function as an alternative to the conditional if-statements. Many excel users use if-statements to test for conditions. And a single statement is easy to grasp, but formula complexity can quickly increase with multiple conditions. Also when using multiple if-statements, part of the formula is repeated over and over again. Making it time consuming to write and audit.
With the SWITCH formula there is an alternative to address these problems. SQL users will see similarities with the CASE formula. I notice myself reaching for the SWITCH formula daily, having almost abandoned IF-statements.
You can find the recorded video at the beginning of this article. Prefer reading? Continue below for the written article!
Table of contents
What does the SWITCH function do?
Let’s have a look at the syntax of the SWITCH formula.
SWITCH( value to evaluate, value = match (1) , value returned when match (1), value = match (2) , value returned when match (2), value = match (3) , value returned when match (3), value returned if there’s no match )
Firstly, the SWITCH function evaluates a value (the first argument is also called the expression). This value can be a cell reference, hard coded value, or any formula that returns a value.
Secondly, the function searches for the first value that matches the expression and returns the corresponding result. The value to match and the corresponding result are acting as pairs. SWITCH supports up to a total 126 matches.
Lastly, you can optionally provide a default value to return when there is no match. So if none of the values (1), (2) or (3) match the expression, the last (optional) argument is returned.
Generally speaking, there are two methods of using the SWITCH.
Method 1: Search for the result of a single expression
The first method enters a value or expression in the first argument that results in a single value. This value is then evaluated against the arguments following. In the below example I use the SWITCH function to generate the names of the days in a week in column “Day Name”. Next to it you find the equivalent nested if-statements.
Notice the following:
- The formula in cell D3 references cell C3 as its first argument. This value corresponds to a day in the week.
- The arguments that follow specify what to do when a value matches. Next to each possible match you find a result that corresponds with the match.
- Lastly, when none of the values match, the optional last argument is returned. In this case, number 8 does not correspond to a weekday and the formula returns “Other”.
- The equivalent IF formula repeats the IF function 7 times.
Method 2: Search for the first expression that equals TRUE
The first example can be useful in some cases. Which is when evaluating against a single value. However, I tend to use the SWITCH function when evaluating multiple expressions.
When you look at the below table containing data , there have been some changes in the ‘Day’ column. You can now find an empty cell and some duplicate values. In this example I want to indicate which days are weekdays and which are weekend. Also I need some error checking for empty cells. Lastly I return a default value when none of the expressions evaluate as true.
Notice the following:
- The first argument for the SWITCH formula now uses the expression TRUE(). By doing that, any expression that follows and evaluates to TRUE() is now considered a match.
- This means that with method 2 you can input different expressions in the same SWITCH function. And each of these expressions will be evaluated for being TRUE(). Even if multiple expressions evaluate to true, the corresponding result will only be returned for the first value that equals true.
- The equivalent IF formula repeats the IF function 3 times.
Advantages of using the SWITCH function
The SWITCH formula has lots of similarities with traditional if-statements. I find myself using the SWITCH function for several reasons:
- Writing conditional statements in SWITCH is quicker. The IF function requires you to repeat the “IF()” part for each condition, whereas SWITCH does not.
- The SWITCH formula takes up less space and looks more organized. The repetition of code in IF statements creates more clutter.
When should I not use the SWITCH function
- The SWITCH function only works with operators that result in an exact match. Conditions testing for greater than (>) or less than (<) are examples of logical operators that do not work with SWITCH. For the same reason I used the ISNUMBER( MATCH() ) construction in method 2. If you want to work with operators that don’t result in an exact match, consider using the IFS formula.
- You will find SWITCH does not work with wildcards.
SWITCH Function Errors
When working with the SWITCH function, if you get an error it could be because of the following:
- #N/A – None of the Values matches the expression and no argument containing the default value is supplied.
- #NAME – If you notice the SWITCH function not working and get this error, you are likely working with an older Excel version. The SWITCH function is not compatible with the earlier versions of 2016, 2013, 2010 or earlier.
If this post added value for you, make sure to share it with your network. And if you enjoy working with multiple conditions, you may get value from the SUMPRODUCT with Multiple conditions post I created earlier. It has follow up posts showing how to easily apply SUMPRODUCT with Multiple OR Condition too. See you next time!