In this Excel tutorial, you learn how to Use SWITCH Function in Excel.
What is SWITCH Function
The SWITCH function in Excel allows you to evaluate an expression against multiple values and return a corresponding result. It’s handy for handling multiple conditions.
How to Use SWITCH Function
Syntax: SWITCH(expression, value1, result1, [value2, result2], …, [default])
- Expression: The value or expression you want to evaluate.
- Values and Results: List values to compare with the expression and the results to return if matched.
- Default (optional): Specify a result to return if none of the values match the expression.
SWITCH Function vs. IF Function Usage
SWITCH is better for scenarios with multiple conditions where you want to return different results based on the first match. It simplifies complex nested IF statements.
Switch Function can make your formulas more concise and readable, especially when dealing with many conditions.
Examples of SWITCH Function Usage
For example, if you want to assign grades based on scores, you can use the SWITCH function like this:
=SWITCH(B2, 90, “A”, 80, “B”, 70, “C”, 60, “D”, “F”)
This formula will return “A” if B2 is 90, “B” if B2 is 80, and so on. If B2 is less than 60, it will return “F” as the default value.
You can also use the SWITCH function with other functions, such as RIGHT, LEFT, MID, etc. For example, if you want to extract the state name from a text string, you can use the SWITCH function like this:
=SWITCH(RIGHT(B3, 2), “UT”, “Utah”, “TX”, “Texas”, “OH”, “Ohio”, “?”)
This formula will return “Utah” if the rightmost two characters of B3 are “UT”, “Texas” if they are “TX”, and so on. If there is no match, it will return “?” as the default value.