SWITCH() Function in Excel – How to use it (With Examples)

SWITCH, previously used only in VBA, is now available as a “SWITCH function” in Excel. Though the name says Switch, it isn’t used to swap items. In fact, it is one of the logical functions that operate as a nested IF function to test multiple condition.

SWITCH function compares different items and returns a result when there’s an exact match. Since this function has better features, you can use it as an alternative to IF or IFS function.

Arguments for SWITCH Function

Excel’s SWITCH function returns a certain value when the expression value matches with the value1. In case the values do not match, it returns the default value you specify in the formula. 

Syntax: SWITCH(expression, value1, result1, [value2, result2],....[ value3, result3], [default])

Function arguments of the SWITCH function are:

• expression: Value (number, date, text) to compare against Value 1.
• value1: Value that you compare against the expression.
• result1: Value you want to return when expression and value1 match.
• [default]: Value to return when the expression and value1 do not match

To explain the SWITCH function in more simpler form, here’s what the argument refers to.

=SWITCH(Value to switch, Value to match1, Value to return if match, Value to return when there’s no match)

Key Points to Note Before You Use the SWITCH Function

• In the SWITCH function, when you enter a text argument, you must enclose all the values inside the “” double quotation marks.
• The SWITCH function does not support logical operators like <>,  >, <, in the Expression function argument. The Expression should be an exact match with one of the values.
• Create and use lookup tables as a reference to enter arguments in the SWITCH function. This not only saves you time but will also help you to pass down the correct argument. 

Examples of SWITCH Function

Example 1: Suppose, we have data with Student’s names, Scores, and Grades. We want to Remark the students based on their grades. For this, we will use the Lookup table from F7:H10 that has the condition of Remarks. Here are the criteria:

• Grade A =Excellent
• Grade B = Good
• Grade C= Fair
• Grade D = Poor

Now, to remark, we entered the formula in the box in Cell D7.

=SWITCH(C7, "D", "Poor", "A", "Excellent", "B", "Good", "C", "Fair")

In the formula, our expression value is cell C7 which is D. Similarly, our value to match is “D”. If the expression matches with the Value1 which is C7=D, it’ll return Poor. Likewise, if C7=A, you’ll get “Excellent.” If C7=B, it’ll result in “Good.” Lastly, if C7=C, it’ll return “Fair.”

Since the value of C7 matched with D, the formula returned “Poor.” We used the Flash-fill to remark the remaining students.

Example 2: Let’s say we have a list of Countries with their scores. To rank them in Gold Silver, and Bronze, we have criteria in the F4:G6 lookup table. 

• Score >=190: Gold
• Score >=185: Silver
• Score >=175: Bronze

Now, to rank the scores, we will enter the following formula

=SWITCH(TRUE, C2>=190, "Gold", C2>=185, "Silver", C2>=175, "Bronze", "N/A")

In this example, we have passed down a boolean as our expression value which is TRUE. So, if C2 >=190, it’ll return either true or false. When it is True, the expression matches with the value and returns the specified result. For Value1, it’ll return Gold. 

Likewise, C2>=185, “Silver” is our value2/result2 and C2>=175, “Bronze” is our value3/result3. In case there are no matches, the formula will return “N/A.” We got Gold. We copied down the formula for other cells using Flash-Fill. 

How to Avoid Errors in the SWITCH function?

When using the SWITCH function, you may encounter these formula errors if you do not enter the correct arguments.

• #NAME? Error: The text values in the formula aren’t double-quoted. 
• #N/A! Error: You haven’t entered the default argument to return when there are no any matches in the formula.

Now that you know the cause of such types of errors, make sure to avoid these mistakes. 

SWITCH and IFS – What’s the Difference?

After looking at the examples of the SWITCH function, you must’ve noticed the function is similar to the IFS function. Both the IFS and SWITCH function tests the criteria of an expression and returns a value if true. So, what are the differences between them?

• Expression argument: In the SWITCH function, you can enter the expression only once in the formula. But, for the IFS function, you need to re-enter the expression for each logical test. 
• Default Argument: If the expression does not match the value, you can enter a default value to return in the SWITCH function. In case of the IFS function, there is no default argument. So, the formula returns a #N/A error when the criteria aren’t True.
• Logical operators (>,<, <>): IFS function’s expression argument supports logical operators. But, the SWITCH function does not take these operators in Expression. However, you can use these operators in the value for the SWITCH function
• Formula Length: Comparatively, the SWITCH function’s formula length is short and easy to understand even for beginners. While, the IFS function’s formula length can get extremely long, making it hard to comprehend, at times.

Alternatives to SWITCH Function

Here are a few alternative functions to the SWITCH function.