This guide explains the =SWITCH() function in Excel. I share what it does and when it is most helpful. I’ll also share some practical examples of its use. The Excel SWITCH function allows you to evaluate a list of items and have Excel return a response based on the item or items you are evaluating.
The SWITCH Function: Explainer Video
If you want to follow along with the explainer video on my YouTube channel, you’ll want an example dataset. I’ve included that below. Just access the dataset and you can follow along in your own local Excel application.
Understanding Syntax
The SWITCH function compares values against a list of values and provides their associated results. If that sounds simple, you’re right. It is!
The syntax of a function shows what’s needed to make it work. Here’s what it looks like for =SWITCH() in Excel.
=SWITCH(expression, value_1, result_1, . . .)
In this case, expression asks for which cell we’re evaluating. In my example, that’s B2. Then it requests the first value. The SWITCH function takes value_1, compares it to its corresponding result, and lists the answer.
In my example below, index number 1, or value_1, corresponds to “Excellent”. The second value I check for is “Good” and the third is “Average”. Microsoft describes this as providing resultN to the corresponding valueN argument.
In other words, you can read the syntax like this:
If "expression" contains "valueN", return "resultN".
That replaces a bunch of conditional statements, so you don’t need to nest IF statements inside of IF statements to make a series of conditions. You can just use the SWITCH formula instead.
Why Use the SWITCH Function?
The SWITCH function is easier to use than having a multitude of nested IF statements, particularly when you need to look at multiple values or conditions within cells. Using SWITCH can simplify your formulas and make them easier to understand and maintain.
The best use cases for SWITCH?
- Limited conditions
- Replace nested IF statements
- Reduce errors during data analysis
- Make formulas easier to read
SWITCH works best when you’re working with a limited number of conditions. The cool thing about this spreadsheet software is the useful number of Excel’s logical functions. SWITCH works well when you have a handful of conditions and a corresponding number of results.
That makes it a perfect replacement for replacing nested IF statements (or reducing them). It also makes the formulas in your cells easier to read. For a beginner, that’s a helpful way to learn logic in a workbook.
For a more experienced user, it can simplify error handling. I’ll explain how in my example below.
A Practical Example
Let me show you an example. Suppose we have a data table listing employees and their corresponding performance ratings for the year. We want to determine the appropriate action based on these performance ratings.
A great way to handle this is to use the SWITCH function. The expression is the cell you want Excel to evaluate. For example, let’s say the expression cell is B2.
- Lookup Value 1: “Excellent” – For employees rated as “Excellent”, the first argument here will be “Promotion”.
- Lookup Value 2: “Good” – For employees rated as “Good”, the action will be “Raise”.
- Lookup Value 3: “Average” – For employees rated as “Average”, the action will be “Extra Break”.
- Lookup Value 4: “Poor” – For employees rated as “Poor”, the action will be “Manager Discussion”.
When writing the SWITCH function in Excel, it will look something like this:
=SWITCH(B2, "Excellent", "Promotion", "Good", "Raise", "Average", "Extra Break", "Poor", "Manager Discussion")You’ll close the formula as shown above. When you copy this formula down your column, Excel returns the appropriate action for each performance rating. This method is much simpler than writing a large nested IF statement for each condition.
This example comes from my video, so you can see everything in more detail there.
Here’s an example, though, that shows just how complex this formula would look with nested IF functions in Excel.
=IF(B2="Excellent", "Promotion",IF(B2="Good", "Raise",IF(B2="Average", "Extra Break",IF(B2="Poor", "Manager Discussion",""))))
Not only is the SWITCH function a simpler choice, it’s also easier to read. But what happens if =SWITCH() checks for a value and there’s no corresponding result? That would return an #N/A unknown error. Here’s how to fix it.
Handling Missing Data
What if there’s no performance rating entered for some employees? By default, this might return an #N/A error. Note that this is different than a circular reference error. It’s just saying that the valueN expressions don’t exist in the expression cell.
You can handle this by adding another criterion to your formula.
For example, you can include a final argument condition for “No Rating”:
=SWITCH(B2, "Excellent", "Promotion", "Good", "Raise", "Average", "Extra Break", "Poor", "Manager Discussion", "No Rating", "Employee has no rating entered")By adding this condition, you can ensure that your formula accounts for missing ratings and provides informative output.
Specific Questions and Answers
If you have a specific questions, please feel free to ask below.
Conclusion
I hope you found this guide on the SWITCH function useful. It’s a powerful tool for simplifying your Excel formulas and handling multiple conditions.
Related Reading:
