In this Excel tutorial, you learn how to use the SWITCH Function in Excel.
What is SWITCH Function
The SWITCH function in Excel is used to evaluate an expression against multiple values and return a corresponding result. It simplifies the handling of multiple conditions, making your formulas more concise and readable compared to nested IF statements.
The SWITCH function allows you to evaluate an expression against a list of values and return a result that matches the first value found. If no match is found, an optional default result can be returned.
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.
Use the SWITCH function to replace complex nested IF statements, making your formulas easier to read and maintain.
