How to Write a Case Statement in Excel (With Example)


A case statement is a type of statement that goes through conditions and returns a value when the first condition is met.

The easiest way to implement a case statement in Excel is by using the SWITCH() function, which uses the following basic syntax:

=SWITCH(A2, "G", "Guard", "F", "Forward", "C", "Center", "None")

This particular function looks at cell A2 and returns the following value:

  • “Guard” if cell A2 equals “G”
  • “Forward” if cell A2 equals “F”
  • “Center” if cell A2 equals “C”
  • “None” if cell A2 does not equal any of the previous values

The following example shows how to use this function in practice.

Example: Case Statement in Excel

Suppose we have the following list of basketball positions:


We’ll use the following SWITCH() function to return a specific position name in column B based on the value in column A:

=SWITCH(A2, "G", "Guard", "F", "Forward", "C", "Center", "None")

We’ll type this formula into cell B2 and then copy and paste it down to every remaining cell in column B:

case statement in Excel
Notice that this formula returns the following values in column B:

  • “Guard” if column A equals “G”
  • “Forward” if column A equals “F”
  • “Center” if column A equals “C”
  • “None” if column A does not equal any of the previous values

Notice that the last value in column B returns a value of “None” since we didn’t specify a specific value to return for “Z” in the formula.

Example: Using SWITCH() for Employee Bonus Categories

Let’s look at another practical example. Suppose you have a spreadsheet with employee performance ratings (A, B, C, D) and you want to assign bonus percentages based on these ratings:

  • Rating “A” receives a 10% bonus
  • Rating “B” receives a 7% bonus
  • Rating “C” receives a 4% bonus
  • Rating “D” receives a 0% bonus

Let’s set up our spreadsheet with employee ratings in column A and their salaries in column B:

In column C, we can use the SWITCH() function to determine the bonus percentage based on the rating:

=SWITCH(A2, "A", 0.1, "B", 0.07, "C", 0.04, "D", 0)

And in column D, we can calculate the actual bonus amount by multiplying the salary by the result of the SWITCH() function:

=B2 * SWITCH(A2, "A", 0.1, "B", 0.07, "C", 0.04, "D", 0)

After applying the formulas to all rows, we get the following results:


Notice how the function correctly assigns the bonus percentage and calculates the bonus amount for each employee based on their performance rating. For example, employees with an “A” rating receive exactly 10% of their salary as a bonus, while those with a “D” rating receive $0.

Wrapping Up

The SWITCH() function in Excel provides a straightforward way to implement case statements without needing nested IF() statements. It works by evaluating an expression and comparing it to a series of values for an exact match. When using SWITCH(), keep in mind that:

  • The function requires exact matches (equals) between the expression and the defined values
  • You can include a default value as the last argument to handle cases where no match is found
  • SWITCH() can make your formulas more readable compared to multiple nested IF() statements

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Find Unique Values from Multiple Columns
Excel: How to Match Two Columns and Return a Third

2 Replies to “How to Write a Case Statement in Excel (With Example)”

  1. The word “contains” is misleading here. Switch works with an exact match.

    So the function returns “Guard” if column A *equals* “G”

    1. Hi Erc,

      Thank you for your valuable feedback on our article. You’re absolutely right that the SWITCH() function works with exact matches, and our use of the word “contains” was technically inaccurate and potentially misleading.

      We’ve updated the article to replace all instances of “contains” with “equals” to more accurately reflect how the SWITCH() function evaluates values. We’ve also added a new example and a “Wrapping Up” section that further emphasizes this point about exact matching.

      We appreciate readers like you who help us improve the accuracy of our content. Thanks for taking the time to point this out!

      Best regards,
      Vinod

Leave a Reply

Your email address will not be published. Required fields are marked *