The SWITCH function in Excel is a logical function that evaluates an expression and returns a value based on a list of possible matches. The SWITCH function can be used to cover multiple possibilities or scenarios without using nested IF statements. The syntax of the SWITCH function is:
=SWITCH(expression, value1, result1, [value2, result2], ... [default])
The SWITCH function has the following arguments:
- expression: The value or expression that you want to test against a list of values.
- value1, value2, …: The values that you want to compare with the expression. You can specify up to 126 pairs of values and results.
- result1, result2, …: The results that you want to return if the expression matches a value. The results can be any valid Excel value, such as text, numbers, dates, or references.
- default: (optional) The value that you want to return if the expression does not match any of the values. If omitted, the SWITCH function will return the #N/A error.
Step by Step
To use the SWITCH function in Excel, follow these steps:
- In a cell, type
=SWITCH(and then enter the expression that you want to evaluate. For example,=SWITCH(A2, - Next, enter the first pair of value and result, separated by a comma. For example,
=SWITCH(A2, "Red", "Apple", - Repeat step 2 for as many pairs of values and results as you need. Make sure to use commas to separate each pair. For example,
=SWITCH(A2, "Red", "Apple", "Yellow", "Banana", "Green", "Pear", - Optionally, enter a default value that you want to return if the expression does not match any of the values, preceded by a comma. For example,
=SWITCH(A2, "Red", "Apple", "Yellow", "Banana", "Green", "Pear", "Unknown") - Close the parentheses and press Enter. The SWITCH function will return the result based on the expression and the list of values and results. For example, if A2 contains “Red”, the SWITCH function will return “Apple”.
Example
Let’s say you have a table of sales data for different products and regions, and you want to assign a commission rate based on the product category and the region. You can use the SWITCH function to create a formula that covers multiple possibilities and returns the appropriate commission rate. Here is the table of data and the formula:
| Product | Category | Region | Sales | Commission Rate |
|---|---|---|---|---|
| Laptop | Hardware | North | 5000 | =SWITCH(B2, “Hardware”, SWITCH(C2, “North”, 0.1, “South”, 0.15, “East”, 0.12, “West”, 0.08, 0), “Software”, SWITCH(C2, “North”, 0.2, “South”, 0.25, “East”, 0.22, “West”, 0.18, 0), “Accessories”, SWITCH(C2, “North”, 0.05, “South”, 0.07, “East”, 0.06, “West”, 0.04, 0), 0) |
The formula uses nested SWITCH functions to evaluate the category and the region, and returns the corresponding commission rate. If the category or the region does not match any of the values, the formula returns 0 as the default value. You can copy the formula to the other cells in the column to get the commission rates for each row. Here is the result:
| Product | Category | Region | Sales | Commission Rate |
|---|---|---|---|---|
| Laptop | Hardware | North | 5000 | 0.1 |
| Tablet | Hardware | South | 3000 | 0.15 |
| Mouse | Accessories | East | 500 | 0.06 |
| Antivirus | Software | West | 1000 | 0.18 |
| Keyboard | Accessories | North | 800 | 0.05 |
Alternatives
The SWITCH function is a convenient way to cover multiple possibilities in Excel, but it is not the only way. Depending on your situation, you may find other functions more suitable or easier to use. Here are some alternatives to the SWITCH function:
- IF function: The IF function is the most basic logical function in Excel, and it can be used to test a condition and return a value based on whether the condition is true or false. The syntax of the IF function is:
=IF(logical_test, value_if_true, [value_if_false])
The IF function has the following arguments:
- logical_test: The condition that you want to check.
- value_if_true: The value that you want to return if the condition is true.
- value_if_false: (optional) The value that you want to return if the condition is false. If omitted, the IF function will return FALSE.
You can use nested IF functions to test multiple conditions and return different values based on the results. However, this can make the formula long and complex, and it may be prone to errors. For example, the formula for the commission rate using nested IF functions would be:
=IF(B2="Hardware", IF(C2="North", 0.1, IF(C2="South", 0.15, IF(C2="East", 0.12, IF(C2="West", 0.08, 0)))), IF(B2="Software", IF(C2="North", 0.2, IF(C2="South", 0.25, IF(C2="East", 0.22, IF(C2="West", 0.18, 0)))), IF(B2="Accessories", IF(C2="North", 0.05, IF(C2="South", 0.07, IF(C2="East", 0.06, IF(C2="West", 0.04, 0)))), 0)))
As you can see, the formula is much longer and harder to read than the SWITCH function.
- IFS function: The IFS function is a logical function that tests multiple conditions and returns a value corresponding to the first true condition. The syntax of the IFS function is:
=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)
The IFS function has the following arguments:
- logical_test1, logical_test2, …: The conditions that you want to check. You can specify up to 127 pairs of logical tests and values.
- value_if_true1, value_if_true2, …: The values that you want to return if the corresponding condition is true.
The IFS function can be used to simplify the nested IF functions, as it does not require parentheses and it stops evaluating once it finds a true condition. For example, the formula for the commission rate using the IFS function would be:
=IFS(B2="Hardware", IFS(C2="North", 0.1, C2="South", 0.15, C2="East", 0.12, C2="West", 0.08, TRUE, 0), B2="Software", IFS(C2="North", 0.2, C2="South", 0.25, C2="East", 0.22, C2="West", 0.18, TRUE, 0), B2="Accessories", IFS(C2="North", 0.05, C2="South", 0.07, C2="East", 0.06, C2="West", 0.04, TRUE, 0), TRUE, 0)
The formula is shorter and easier to read than the nested IF functions, but it is still longer and more repetitive than the SWITCH function.
- CHOOSE function: The CHOOSE function is a lookup function that returns a value from a list of values based on a given index number. The syntax of the CHOOSE function is:
=CHOOSE(index_num, value1, [value2], ...)
The CHOOSE function has the following arguments:
- index_num: The index number that specifies which value to return from the list. The index number must be between 1 and 254, or a formula that evaluates to a number between 1 and 254.
- value1, value2, …: The list of values that you want to choose from. You can specify up to 254 values of any type.
The CHOOSE function can be used to return a value based on a list of possible matches, similar to the SWITCH function. However, the CHOOSE function requires an index number, not an expression, to determine the value to return. Therefore, you may need to use other functions, such as MATCH, to convert the expression to an index number. For example, the formula for the commission rate using the CHOOSE function would be:
=CHOOSE(MATCH(B2, {"Hardware", "Software", "Accessories"}, 0), CHOOSE(MATCH(C2, {"North", "South", "East", "West"}, 0), 0.