SWICTH function.Displays the first result matching the specified value.(Microsoft Excel)
Displays the first result matching the specified value.
How it works
=SWITCH(expression, value1 to 126, result1 to 126, default)
| Argument | Omission | Specify |
|---|---|---|
| expression | Required argument. | Specify the expression to be evaluated. |
| value1 to 126 | value1 is required. | Specify the match condition as a number or a string. Partial specification with wildcards is not possible. |
| result1 to 126 | result1 is required. | Specifies the result corresponding to a value match. If there are multiple matches, the first value takes precedence. Mathematical expressions and functions can also be used. |
| default | #N/A Error | The value if there is no matching value. If there are an odd number of arguments, the last argument is the "default". |
Differences from the CHOOSE Function.
The SWITCH function is an extended version of the CHOOSE function.
The differences are as follows.
Advantages
String as well as numerical values can be specified as conditions
No need to set fictitious results for non-consecutive numbers
Numerical values other than 1-254 can be specified as conditions
No need to use IFERROR or other functions to specify a result when there is no corresponding value because of the "default" argument
Inferiority
Sequential numbers from 1 to 254; if all are present, the CHOOSE function is better.
Example Results
Used in conjunction with the WEEKDAY function to display the day of the week corresponding to a date.
If there is no corresponding result, "#N/A error" is returned.
For CHOOSE function, "#VALUE! error".
After the last "result", another argument can be added to become the "default" if there is no match.
While the CHOOSE function requires an IFERROR function, the SWITCH function can be used by itself.
Also, the numbers do not have to be consecutive and there is no range restriction from 0 to 254.
Unlike the CHOOSE function, it is possible to use a string as a condition.
However, partial match searches using wildcards are not possible.
Cases where the SWICTH function is not appropriate.
The CHOOSE function is suitable for simple expressions.
In the following cases, other functions should be used.
XLOOKUP function (VLOOKUP function)
In the following cases, it is better to use the XLOOKUP function (VLOOKUP function) to manage the worksheet and make the formulas more readable.
- There are a very large number of value types.
- SWICTH function with the same syntax is needed for multiple cells.
IFS function
The IFS function is appropriate if you want to specify a range of values for the decision condition.
Spill
If you specify the argument "expression" the cell range, it will be Spil.
---