Function Reference: SWITCH
| Syntax | ||
| =SWITCH(expression,value1,result1,default_or_value2,[result2,default_or_value3]...[value126,default_or_value127]) | ||
| Purpose / Description | ||
| Returns a value associated with the first listed value that matches the result of the expression given The value of expression is derived and that result is compared to every second argument. If it matches, the associated result is returned, otherwsie the value of the final argument, the default value is returned. | ||
| Arguments | ||
| Name | Type | Description |
| expression | any | A constant, reference or nested expression whose value is to be compared to a list of values to determine the result. Usually it will be a reference or nested expression. |
| value1 | any | A constant, reference or nested expression that specifies the first value to be compared to expression. |
| result1 | any | A constant, reference or nested expression that specifies the result to be returned if expression matches value1. |
| default_or_value2 | any | A constant, reference or nested expression that specifies either, the default value returned, if this is the last argument, or that specifies the second value to be compared to expression. |
| [result2]..[result126] | any | An optional constant, reference or nested expression that specifies the result to be returned if the associated valuen matches the value of expression. |
| [default_or_value3]...[default_or_value127] | any | An optional constant, reference or nested expression that specifies either, the default value returned, if this is the last argument, or that specifies the nth value to be compared to expression. |
| Return Values | ||
| Type | Specific Value | Description |
| any | The resultn associated with the first valuen that matches the value of expression. As there is a default value always specified, there is no error associated with not making a match. | |
| error | Any errors referenced by arguments are returned due to propagation of error values. | |
| Notes / Comments / Advice / Warnings | ||
| Note: | This function was added in Excel 2016, however we are not seeing it widely used, this is something that ought to change, as this provides a much more effective and transparent solution to handling multiple outcomes from a single tested condition. | |
| Note: | This function requires an even number of arguments to be provided. One resultn for every valuen. | |
| Advice: | Where the value of a single cell or expression has multiple dependent conditions, this is a better solution than nested IF functions, for example =SWITCH(A1,1,"one",2,"two",3,"three","other") is much easier to read than =IF(A1=1,"one",IF(A1=2,"two",IF(A3=3,"three","other"))), especially if the rules become complex. |
|
| Examples: | Open / download sample workbook: SWITCH.xlsx | |