This guide will explain how to write a case statement in Excel using the SWITCH function.
The rules for using the SWITCH function in Excel are the following:
- Since Excel limits its function to 254 parameters, the
SWITCHfunction only supports up to 126 pairs of value and result arguments. - The
SWITCHfunction only performs an exact match. So we cannot include logical operators such as lesser than or greater than to find a match. - The expression argument can be another formula that will return a specific value.
Excel is a popular tool that is used for many different situations. For example, we often use Excel for organizing and analyzing data. And we use it to perform difficult calculations.
Specifically, we can use it to write a case statement. So a case statement is a specific statement that passes through conditions. The function returns a specific value when the first condition is met.
When we want to go through a list of data and return specific values, this is an example of a case statement. Since Excel contains multiple built-in functions, it is easy to create a formula to write a case statement for us. And the SWITCH function is the most appropriate function to use.
Let’s take a sample scenario wherein we must write a case statement in Excel.
Suppose you have a list of data. And you want to return a specific value as you inspect each data. So you create a formula using the SWITCH function to write a case statement for this data set.
Before we move on to a real example of writing a case statement in Excel, let’s first learn about the SWITCH function in Excel.
The Anatomy of the SWITCH Function
The syntax or the way we write the SWITCH function is as follows:
=SWITCH(expression, value1, result1, [default_or_value2, result2]...)
Let’s take apart this formula and understand what each term means:
- = the equal sign is how we begin any function in Excel.
- SWITCH() is our
SWITCHfunction. The function is used to evaluate an expression against a list of values. Then, the function will return a result corresponding to the first matching value. The function will return an optional default value. - expression is a required argument. So it refers to the expression we want to evaluate.
- value1 is also a required argument. And it is the value we want to be compared to the selected or inputted expression.
- result1 is another required argument. So it refers to a result we want to be returned if the selected or inputted value matches the expression.
- default_or_value2 is an optional argument. This argument refers to a default value to be returned.
Great! Now we can dive into a real example of writing a case statement in Excel.
A Real Example of Writing a Case Statement in Excel
Let’s say we have a data set containing a list of values. And we need to evaluate each value in the data set and return a specific value that matches. So our initial data set would look like this:
In this case, our data set is a list of one letter corresponding to a specific fruit. So O for orange, A for apple, B for banana, and G for grapes. And we want to create a case statement that will go through each value and return the corresponding fruit name.
And we will be using the SWITCH function to create this formula to write a case statement in Excel. So the SWITCH function evaluates each value or expression and returns a corresponding value when a match is found.
In this case, we can simply use the SWITCH function to evaluate each expression or value. We will select the specific expression we want to evaluate. We will input the returned values we want.
For example, the function will return orange if the expression we selected or inputted is O. We can indicate in the function to return a specific value such as none or no match found.
Once we have inputted the SWITCH function formula in the first cell of the data set, we can simply drag it down and copy the formula to the other cells. So the SWITCH function will return a specific fruit name in another column depending on the value in the column containing the data set.
Afterward, the formula will return the corresponding values. In this case, the function will return orange if it has O, apple if it has A, banana if it contains B, and grapes if it contains G. For example, there is no match. The formula will return No Match Found.
So the last value in the data set has no match since we did not input a specific value in the formula to return for D. Our final data set would look like this:
You can make your own copy of the spreadsheet above using the link attached below.
Amazing! Now let’s move on and discuss the steps of how to write a case statement in Excel using the SWITCH function.
How to Write a Case Statement in Excel
In this section, we will explain the step-by-step process of how to write a case statement in Excel using the SWITCH function. Furthermore, each step contains detailed instructions and pictures to guide you along the process.
To apply this method in your work, simply follow the steps below.
1. First, we will create a new column wherein we can input the results of our formula. In this case, we will place it beside the data set and label the column “Fruit Name”. Then, we can input the formula in the first cell of this column. So type in an equal sign to start the SWITCH function.
Since we need to evaluate one expression or value at a time, we will select the first cell of the data set to evaluate. So input the final formula “=SWITCH(B2, “O”, “Orange”, “A”, “Apple”, “B”, “Banana”, “G”, “Grapes”)” in cell C2. Lastly, click the Enter key to return the result.
2. Secondly, we will drag the Fill Handle tool down to copy the formula and apply it to the other cells in the column. So this will return the corresponding values of the entire data set.
3. We will input a specific value at the end of the formula. In this case, we will return “No Match Found”. So our formula would be “=SWITCH(B2, “O”, “Orange”, “A”, “Apple”, “B”, “Banana”, “G”, “Grapes”, “No Match Found”)”.
Finally, press the Enter key to apply this formula.
4. And tada! We have successfully used the SWITCH function to write a case statement in Excel.
And that’s pretty much it! We have explained how to write a case statement in Excel using the SWITCH function. You can apply this method in your work whenever you need to write a case statement.
Are you interested in learning more about what Excel can do? You can now use the SWITCH function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.
