How to Write a Case Statement in Excel

In this tutorial, you will learn how to use an write a case statement in Excel.

A case statement is also known as a case expression. When the first condition is satisfied, the case expression iterates over conditions and returns a value (like an if-then-else statement). Therefore, it will stop reading and return the outcome if a condition is true. It returns the value in the else clause if none of the criteria are true.

This is an illustration of a case statement, which is used when we wish to iterate through a list of data and return particular results. Excel has a variety of built-in functions, so it is simple to build a formula that would generate a case statement for us. And the best function to employ is the SWITCH function.

Once ready, we’ll get started by utilizing real-world examples to show you how to use an write a case statement in Excel using the SWITCH function.

Anatomy of SWITCH Function

SWITCH Function

=SWITCH(Value to switch, Value to match1…[2-126], Value to return if there’s a match1…[2-126], Value to return if there’s no match)

The SWITCH function compares a single value (referred to as the expression) to a set of values and outputs the result that matches the first matching value. An optional default value could be returned if there is no match.

Excel’s function is only allowed 254 parameters, hence the SWITCH function can only accept 126 pairs of value and result arguments.

Only an exact match is performed by the SWITCH function. Thus, we are unable to include logical operators like

Write a Case Statement in Excel

Before we begin we will need a group of data to write a case statement in Excel.

Step 1

First, you need to have a clean and tidy group of data to work with.

Step 2

In this example, the names of the sports are represented by an alphabet.

Step 3

To write a case statement you can simply insert this formula =SWITCH(A2, “B”, “Basketball”, “R”, “Rugby”, “S”, “Swim”, “G”, “Gym”, “None”).

Step 4

Once you are done, apply the same formula to the rest of the data and your Excel will look like this.