The SWITCH function in Excel is a powerful tool used to evaluate an expression against a list of values and return a result corresponding to the first matching value. It simplifies the process of checking multiple conditions, acting as an alternative to nested IF statements. The SWITCH function is particularly useful when you have a single expression that can result in multiple possible values, and you want to handle each case differently.
Syntax
| Formula | Description |
|---|---|
| =SWITCH(expression, value1, result1, [value2, result2], ..., [default]) | Evaluates an expression against a list of values and returns the result of the first match found. |
- expression: The expression that you want to evaluate.
- value1, value2, ...: The values to compare with the expression.
- result1, result2, ...: The results to return when the expression matches a corresponding value.
- [default]: An optional value to return if no match is found.
Example
Let’s consider a scenario where a company uses a rating system to evaluate the performance of its employees. The ratings are as follows:
- "A" for Excellent
- "B" for Good
- "C" for Average
- "D" for Poor
The SWITCH function can be used to assign performance levels based on these ratings.
| Employee | Rating | Performance Level |
|---|---|---|
| John | A | =SWITCH(B2, "A", "Excellent", "B", "Good", "C", "Average", "D", "Poor") |
| Jane | C | =SWITCH(B3, "A", "Excellent", "B", "Good", "C", "Average", "D", "Poor") |
| Mark | B | =SWITCH(B4, "A", "Excellent", "B", "Good", "C", "Average", "D", "Poor") |
In this example, the SWITCH function evaluates the rating in column B and returns the corresponding performance level in column C.
Practice Exercise 1
Task: Use the SWITCH function to convert numeric grades into letter grades based on the following scale:
- 90-100: "A"
- 80-89: "B"
- 70-79: "C"
- 60-69: "D"
- Below 60: "F"
Hint: Consider how to structure the SWITCH function to handle ranges of grades.
Practice Exercise 2
Task: Use the SWITCH function to categorize sales figures into different levels based on the following criteria:
- $100,000 or more: "Top Performer"
- $75,000 to $99,999: "Strong Performer"
- $50,000 to $74,999: "Average Performer"
- Less than $50,000: "Needs Improvement"
Hint: You will need to handle different ranges of sales using the SWITCH function.
Solution for Exercise 1:
=SWITCH(TRUE, B2>=90, "A", B2>=80, "B", B2>=70, "C", B2>=60, "D", B2<60, "F")Explanation: This formula evaluates the numeric grade in B2 against the conditions specified, starting from the highest grade. The TRUE at the beginning allows the function to check each condition in sequence.
Solution for Exercise 2:
=SWITCH(TRUE, B2>=100000, "Top Performer", B2>=75000, "Strong Performer", B2>=50000, "Average Performer", B2<50000, "Needs Improvement")Explanation: This formula categorizes the sales figure in B2 based on the sales performance criteria. The TRUE condition ensures that the SWITCH function evaluates the correct category based on the value in B2.