How to Use SWITCH Function

In this Excel tutorial, you learn how to use the SWITCH Function in Excel.

What is SWITCH Function

The SWITCH function in Excel allows you to evaluate an expression against multiple values and return a corresponding result. It’s handy for handling multiple conditions.

How to Use SWITCH Function

Syntax: SWITCH(expression, value1, result1, [value2, result2], …, [default])

  • Expression: The value or expression you want to evaluate.
  • Values and Results: List values to compare with the expression and the results to return if matched.
  • Default (optional): Specify a result to return if none of the values match the expression.

SWITCH Function vs. IF Function Usage

SWITCH is better for scenarios with multiple conditions where you want to return different results based on the first match. It simplifies complex nested IF statements.

Switch Function can make your formulas more concise and readable, especially when dealing with many conditions.

Examples of SWITCH Function Usage

For example, if you want to assign grades based on scores, you can use the SWITCH function like this:

=SWITCH(B2, 90, “A”, 80, “B”, 70, “C”, 60, “D”, “F”)

This formula will return “A” if B2 is 90, “B” if B2 is 80, and so on. If B2 is less than 60, it will return “F” as the default value.

excel switch function grade

You can also use the SWITCH function with other functions, such as RIGHT, LEFT, MID, etc. For example, if you want to extract the state name from a text string, you can use the SWITCH function like this:

See also  Time Value of Money (TVM) Analysis in Excel

=SWITCH(RIGHT(B3, 2), “UT”, “Utah”, “TX”, “Texas”, “OH”, “Ohio”, “?”)

This formula will return “Utah” if the rightmost two characters of B3 are “UT”, “Texas” if they are “TX”, and so on. If there is no match, it will return “?” as the default value.