The Excel SWITCH function is a neat alternative to nested IF functions or the IFS function.
It is a useful substitute and a real timesaver compared to complex nested IF formulas. It is more compact and easier to read.
Watch the Video
SWITCH Function Syntax
The SWITCH function compares a value (known as the expression) against a list of values, and returns the result for the first matching value. If a match is not found, a default result can be returned.
=SWITCH(expression, value1, result1, [default_or_value2, result2])
Expression: is the value to be tested against the list of values. This is typically a reference to a cell value such as A2.
Value1: is the first value (SWITCH can evaluate up to 126 values) to be compared against the expression.
Result1: is the action to perform if its corresponding value is a match to the expression.
Default: This optional default value provides SWITCH with an else argument. This is the result to return if there is no match found.
Let’s see some SWITCH function examples.
SWITCH Function with Text Values
The SWITCH function is a great option when testing text values.
In this example, the value in column C is used for the expression (sometimes referred to as the case), and the SWITCH function returns the discount for the first matching text value.
A default value of 0% is returned if there is no matching value found. In this example, 0% is returned for the “Basic” level.
=SWITCH(C3,"Executive",20%,"Premium",15%,"Plus",10%,0%)
We could have entered “Basic” as one of the values in the list to match, and this would have been better practise, but I wanted to demonstrate the use of the default value. Functions such as IFS do not have a default value argument so it is a strength of SWITCH.
SWITCH Function Versus IFS and Nested IF Functions
When you compare the SWITCH function to a nested IF or IFS function version of the same formula, you can see that SWITCH is slightly smaller. The real difference is that SWITCH is a more compact and concise formula.
SWITCH only refers to the expression once, which is great. While IFS repeats the reference to the expression in each logical test it performs. To handle the default value, IFS has TRUE value at the end to perform an else case.
The nested IF formula is the messiest approach with multiple IF functions, cell references and brackets.
This is just 3 tests. If there were many more the difference would have been glaring.
Testing Numeric Values with SWITCH
Let’s now use the SWITCH function to test numeric values. This is something that many users do not feel is possible, but the trick is to use TRUE as the expression.
In the following formula, conditional tests have been used for each value in the list to determine the discount to apply. the first test to evaluate to TRUE would be a match for the expression and the SWITCH function returns that discount.
=SWITCH(TRUE,D3>=1500,20%,D3>=1000,15%,D3>=500,10%,0%)
The order of the list of values is important. The SWITCH function returns the first matching value in the list, so for this example, testing the largest value first is important.
Let’s say the list of values were in reverse. In this scenario, incorrect results are returned.
All expressions, except one, are a match for the first value in the list, as they are greater than or equal to 500. Therefore 10% is returned.
The default value of 0% is returned for the final customer.
Using AND with SWITCH in Excel
When setting the expression argument in SWITCH to TRUE, you can perform any logical text in the list of values. This gives us great flexibility. Any test that evaluates to TRUE could be used.
Common use cases for this would involve the OR and AND function in Excel.
In this formula, an additional value has been added to the list to return a 25% discount if the customer has special status and has spent 1500 or more. The fourth customer in the list qualifies for this discount.
=SWITCH(TRUE,
AND(C3="Y",D3>=1500),25%,
D3>=1500,20%,
D3>=1000,15%,
D3>=500,10%,
0%)
In this example, the SWITCH function is split over multiple lines using the Alt + Enter shortcut in Excel. This makes the different conditional tests easier to read and digest.
Switch Functions from Drop-Down Selection
Moving onto a more elaborate example of the SWITCH function in Excel, we will use SWITCH to perform a different formula dependent on a value selected from a drop-down list.
I showed a similar example to this with the fantastic AGGREGATE function in Excel which can execute 19 different Excel functions, but by using a SWITCH formula we can use any Excel function.
This example demonstrates that by deploying SUMIFS and COUNTIFS functions for the matches in the list. This time, we ignore the optional default value argument.
=SWITCH(
C2,
"Sum",SUMIFS(tblSales[Sales],tblSales[Region],B3#),
"Average",AVERAGEIFS(tblSales[Sales],tblSales[Region],B3#),
"Count Large",COUNTIFS(tblSales[Region],B3#,tblSales[Sales],">=100")
)
The drop-down menu is in cell C2. SWITCH tests the selected value for an exact match against the values in its list. When there is a match found, the corresponding formula is executed.
Dynamic Chart Title with SWITCH Function in Excel
And for a final Advanced Excel formula example, we use the SWITCH function in Excel to create a dynamic chart title.
This formula is similar to the previous one as it uses the value from the drop-down in C2 as the expression again. However, this time the SWITCH function returns a string to be used in a chart title.
=SWITCH(
C2,
"Sum", "Total Sales for 2024 is "&TEXT(SUM(C3#),"#,#"),
"Average", "Average Sales by Country for 2024",
"Count Large", "Large Orders by Country for 2024"
)
These examples demonstrate how useful the Excel SWITCH function is for complex logical formulas. It certainly has advantages over Excel functions such as IFS.
