The Excel SWITCH function evaluates an expression against a list of values and returns a result corresponding to the first matching value. If there is no match, SWITCH returns an optional, user-defined value.
Syntax, Arguments, return value
The SWITCH function matches multiple values, returns the first match, and appears in Excel 2019.
Syntax:
=SWITCH (expression, value1/result1, [default or value2, result2,…])
Arguments:
- expression – The value or expression to match against.
- value1/result1 – The first value and result pair
- value2/result2 – The second value and result pair [optional argument]
- default – Default (user-defined) value in case no match is found [optional argument]
How the SWITCH function works
The SWITCH function compares a single value against a list and returns the first match’s result. SWITCH returns a user-defined default value if you have not matched items. The first argument (expression) is a constant. You can use cell references or formulas.
We have created a sample data set to show how the function works. The range F3:G6 contains a helper table with the given Category and the corresponding discounts. We want to find the matching pairs in column D based on the helper table.
Apply the formula in cell D4: =SWITCH(C4,”A”,”20%”,”B”,”30%”,”C”,”40%”,”Not found.”)
As we mentioned above, the first argument of the SWITCH function is a constant. In this case, we are using a cell reference, C4. Let’s evaluate the formula based on the SWITCH function!
The formula uses three tests to find the proper discount for Category “B” and uses the corresponding pair. The result is 30%.
Take a closer look at the result in cell D8. Because there are no pairs for category D, the function will use the “Not found” text string. You can modify the value if no match is found.
Tip: The SWITCH function can handle a maximum of 126 values/results pairs.
Workaround with Logical Operators and SWITCH
SWITCH function only operates with an exact match. You can not use logical operators as arguments with the standard syntax.
A possible workaround is to use TRUE as the first argument. Let’s combine the switch function with the AND function.
For example, we want to apply a 20% discount if the price is between 100 and 200. So, use the “greater than or equal to” and the “less than equal to“.
=SWITCH(TRUE, AND(C4>=100, C4<=200),”20%”,”No discount”)
Differences between IFS and SWITCH functions
In Excel, you can choose powerful built-in functions to test multiple conditions.
Self-contained formulas based on nested IF functions can be a bit complicated. For easy comparison, we will write two formulas for the same purposes.
Our conditions are the following:
If the first character in cell B4 is:
- “Apple”, write letter “A”
- “Banana”, write letter “B”
- “Lemon”, write letter “L”,
- in any other cases return the “Not found” string.
The formula if we use the SWITCH function:
=SWITCH(B4,”Apple”,”A”,”Banana”,”B”,”Lemon”,”L”,”Not found”)
The solution using the IFS function:
=IFS(B4 = “Apple”,”A”,B4=”Banana”,”B”,B4=”Lemon”,”L”)
SWITCH produces easy-to-read output if you use many conditions because it uses a single expression as a first argument. However, in the case of the IFS function, you need to repeat the expression for every test. Lastly, IFS can not use a default value if no match is found.
Tip: To avoid #N/A errors, add a final condition at the end of the IFS function:
=IFS(B4 = “Apple”,”A”,B4=”Banana”,”B”,B4=”Lemon”,”L”,TRUE,”Something went wrong.”)