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. 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 to return when match 1",
value = "match 2" , "value to return when match 2",
value = "match 3" , "value to return when match 3",
"value returned when 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 of 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.
Methods
Generally speaking, there are two methods of using the SWITCH.
Method 1: Find 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 the 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: Find the first expression that returns 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 belong to the weekend. Also, I need some error checking for empty cells. Lastly, I return a default value when none of the expressions evaluates 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. Each of these expressions will be evaluated for being TRUE(). Even if multiple expressions are evaluated as 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.
Conclusion
This article described how you can use SWITCH to build your logic and simplify your formulas. 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 Conditions too. See you next time!
Recommended Reading…
Power Query Foundations
Beginners Guide to Power Query
List Functions (200+ Examples)
Text Functions (150+ Examples)
Creating Tables (40+ Examples)
Generate the Ultimate Date Table
Advanced Topics
Master List.Generate with Easy Examples
Deep Dive into List.Accumulate
Sorry I am late to this blog article, but I thought you, Greg and your readers might find the following to be of interest. My formula for you Method 1 question is a lot shorter than the ones posted in the article…
=TEXT(C3,”[>7]””Other””;dddd”)
My formula for Method 2 is only 80 characters long and uses only 3 function calls instead of the 4 function calls for your and Greg’s formulas…
=CHOOSE(IF(C3>7,4,TEXT(C3,”[=0]1;[<6]2;3")),"Empty","Weekday","Weekend","Other")
Actually, for Method 2, only 70 characters long and using only 2 function calls…
=IF(A1>7,”Other”,TEXT(A1,”[=0]””Empty””;[<6]""Weekday"";""Weekend"""))
Regarding Method 2, you original setup seemed to exclude negative numbers as well as the number zero from being in a cell. I wondered though, what would a formula look like if we allowed those values. Assuming the number 0 should return “Other” whereas an empty cell should return “Empty”, here is what I came up with. My formula is now 88 characters long (still shorter than the other now to be considered less robust formulas offered by you and Greg)….
=IF((A1"")*(ABS(A1-4)>3),"Other",TEXT(A1,"[=0]""Empty"";[<6]""Weekday"";""Weekend"""))
I am guessing your comment processor eats up the “not equal” sign (a less than symbol followed by a great than symbol) as the one that should be between the A1 and the “” at the beginning of my last posted formula is missing.
Rick, your formulas are magically short again, thanks for adding it to the thread. Whereas the focus of the post was to learn the SWITCH syntax, your additions are really worthwhile to learn from.
I tried editing your code with the “<>” by surrounding the formula using : < code> and < /code>. Is it now as you wanted?
I just learned about the SWITCH formula and am interested in it, but I’m trying to find a real-world example of where I would use it and would be better than IF or IFS.
In your example above, if you use an IFS statement, it’s less characters than SWITCH.
IFS = 100 characters
=IFS(C3=””,”Empty”,OR(C3=6,C3=7),”Weekend”,ISNUMBER(MATCH(C3,{1,2,3,4,5},0)),”Weekday”,TRUE,”Other”)
SWITCH = 105 characters
=SWITCH(TRUE(),C3=””,”Empty”,OR(C3=6,C3=7),”Weekend”,ISNUMBER(MATCH(C3,{1,2,3,4,5},0)),”Weekday”,”Other”)
Hi Greg.
The IFS function is very similar to SWITCH. In your example as you say it saves some characters and the result is identical. Some reasons I can imagine why people use it:
1. The function is found in other languages, and people are used to it.
2. The function was added to Excel 2016, when the IFS function didn’t exist yet. And once we humans get used to using a certain function… some stick with it !
3. Lastly, in the first example in this blog article, one does not have to repeat the cell C3 in each condition. Whereas the IFS function would repeat it.
Other than that, take the one you feel comfortable with. Hope that helps!
Rick