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.

**NOTE:**SWITCH Function is available in Excel Web, Office 365, Excel 2019 and later versions.

## 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**#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.

Function | Syntax | Description |

IF | =IF(logical_test, value_if_true, [value_if_false] | Tests a criteria and returns a value when the result is either true or false. Use the IF function if you have only one condition to test. |

IFS | =IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], …) | Returns a value when the logical test is true. IFS functions are best when you have multiple criteria to test. |

VLOOKUP | =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup) | Returns the lookup value from the specified column in a table range. VLOOKUP Function is a savior to extract value from an extremely large data set. |