Overview of the SWITCH Function
The Excel SWITCH function in Excel allows you to evaluate an expression against multiple values and return a specific result based on the first match found. It’s particularly useful when you have multiple conditions to check, offering a cleaner alternative to nested IF statements. The SWITCH function is designed to simplify formulas and improve readability. For more information, you can check the official Microsoft SWITCH function documentation.
Syntax
=SWITCH(expression, value1, result1, [value2, result2], …, [default])
Arguments
- expression (required): The value or expression you want to evaluate.
- value1 (required): The first value to compare against the expression.
- result1 (required): The result to return if value1 matches the expression.
- value2, result2 (optional): Additional values and results to check if value1 doesn’t match.
- default (optional): The result to return if no matches are found.
Version
Excel 2019 and Excel for Microsoft 365
Purpose
The purpose of the SWITCH function is to evaluate multiple possible values for an expression and return a specific result based on the first match. This function simplifies formulas that require multiple IF statements, making them easier to read and manage.
=SWITCH(expression, value1, result1, [value2, result2], …, [default])
Examples of the SWITCH Function
Example 1: Basic SWITCH Function
Suppose you have a numerical rating in cell B4. Based on this rating, you want to assign a performance level:
- 1 for “Pass”
- 2 for “Fail”
If we enter 2 in cell B4, we get:
The formula we have in cell B6 is:
=SWITCH(B4, 1, “Pass”, 2, “Fail”, “Invalid Rating”)
Result:
- If B6 is 1, the formula returns “Pass”.
- If B6 is 2, it returns “Fail”.
- If B6 doesn’t match any value, it returns “Invalid Rating” as the default.
Let’s see what difference adjusting the expression from 2 to 4 makes:
We get Invalid Rating, which is expected as 4 is not one of our expected values.
Example 2: SWITCH Function with Day Abbreviations
Suppose you have an abbreviated day name in cell B4 (e.g., “Mon”), and you want to display the full day name.
The formula we have in cell B6 is:
=SWITCH(B4, “Mon”, “Monday”, “Tue”, “Tuesday”, “Invalid”)
Explanation:
- If B4 contains “Mon,” the formula returns “Monday”.
- If it’s “Tue,” it returns “Tuesday”.
- For any other entry, it outputs “Invalid”.
Example 3: SWITCH Function for Basic Grades
Suppose B4 contains a letter grade, and you want to assign a short description.
The formula we have in cell B6 is:
=SWITCH(B4, “A”, “Top”, “B”, “Good”, “C”, “Avg”, “Invalid”)
Result:
- If B4 is “A”, the function returns “Top”.
- If B4 is “B,” it returns “Good”.
- If B4 is “C,” it returns “Avg”.
- For any other entry, it returns “Invalid”.
Notes
- Default Argument: Always use a default argument as a fallback to avoid errors if no match is found.
- Comparing SWITCH to IF: The SWITCH function can often replace long, nested IF statements, making formulas easier to read and maintain. For more complex comparisons, check out Microsoft’s official documentation on the IF function.
- Case Sensitivity: The SWITCH function is case-sensitive, meaning “a” and “A” are treated as different values.
Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has been consulting on and teaching best practices in Microsoft Excel to over 400 companies across 30+ countries.
Want to learn more?
Join My Excel Academy Today!
This All-Access Pass Includes:
- Access to All My Excel Academy Courses
- 20+ hours of on-demand video
- 1-on-1 instructor support
- Mobile learning supported
- Frequently updated content
- Learn at your own pace
- 500+ practice problems
- Certificate of completion
30-Day Money-Back Guarantee
Want to learn more?
Join My Excel Academy Today!
All-Access Pass Includes:
- Access to All My Excel Academy Courses
- 20+ hours of on-demand video
- 1-on-1 instructor support
- Mobile learning supported
- Frequently updated content
- Learn at your own pace
- 500+ practice problems
- Certificate of completion
30-Day Money-Back Guarantee
Want to learn more?
Join My Excel Academy Today!
30-Day Money-Back Guarantee
This all-access pass includes:
- Access to all My Excel Academy Courses
- 20+ hours of on-demand video
- 1-on-1 instructor support
- Mobile learning supported
- Frequently updated content
- Learn at your own pace
- 500+ practice problems
- Certificate of completion
Don’t hesitate – join now!
