The **Excel SWITCH function** evaluates an expression against a list of values and returns a result corresponding to the first matching value. If there is no match, SWITCH returns an optional, user-defined value.

## Syntax, Arguments, return value

The SWITCH function matches multiple values, returns the first match, and is appeared in Excel 2019.

**Syntax:**

**=SWITCH (expression, value1/result1, [default or value2, result2,…])**

**Arguments:**

**expression**– The value or expression to match against.**value1/result1**– The first value and result pair**value2/result2**– The second value and result pair [optional argument]**default**– Default (user-defined) value in case of no match is found [optional argument]

## How the SWITCH function works

The SWITCH function **compares a single value against a list** and returns the first match’s result. SWITCH returns a user-defined default value if you have not matched items. The first **argument** (expression) is a constant. You can use cell references or formulas.

We have created a sample data set to show how the function works. The range F3:G6 contains a helper table with the given Category and the corresponding discounts. We want to find the matching pairs in column D based on the helper table.

Apply the formula in cell D4: **=SWITCH(C4,”A”,”20%”,”B”,”30%”,”C”,”40%”,”Not found.”)**

As we mentioned above, **the first argument of the SWITCH function is a constant**. In this case, we are using a cell reference, C4. Let’s evaluate the formula based on the SWITCH function!

**The formula uses three tests to find the proper discount for Category “B” and uses the corresponding pair.** The result is 30%.

Take a closer look at the result in cell D8. Because there are no pairs for category D, the function will use the **“Not found”** text string. You can modify the value in case of no match is found.

**Tip: **The SWITCH function can handle a maximum of 126 values/results pairs.

### Workaround with Logical Operators and SWITCH

SWITCH function only operates with an exact match. You can not use **logical operators** as arguments with the standard syntax.

**A possible workaround is using TRUE as a first argument.** Let us combine the switch function with the **AND function**.

In the example, we want to apply a 20% discount if the Price is between 100 and 200. So, use the “**greater than or equal to**” and the “less than equal to”.

**=SWITCH(TRUE,AND(C4>=100, C4<=200),”20%”,”No discount”)**

### Differences between IFS and SWITCH functions

In Excel, you can choose powerful built-in functions to test **more than one condition**.

Self-contained formulas based on nested IF functions can be a bit complicated. For easy comparison, we will write two formulas for the same purposes.

**Our conditions are the following:**

If the first character in cell B4 is:

- “Apple”, write letter “A”
- “Banana”, write letter “B”
- “Lemon”, write letter “L”,
- in any other cases return the “Not found” string.

**The formula if we use the SWITCH function:**

=SWITCH(B4,”Apple”,”A”,”Banana”,”B”,”Lemon”,”L”,”Not found”)

**The solution using the IFS function:**

=IFS(B4 = “Apple”,”A”,B4=”Banana”,”B”,B4=”Lemon”,”L”)

**SWITCH produces easy to read output** if you use many conditions because it uses a single expression as a first argument. However, in the case of the IFS function, you need to repeat the expression by every test. **Last but not least, IFS can not use a default value in case no match is found.**

**Tip:** To avoid #N/A errors, add a final condition at the end of the IFS function:

=IFS(B4 = “Apple”,”A”,B4=”Banana”,”B”,B4=”Lemon”,”L”,TRUE,”Something went wrong.”)

**Additional resources**