| Syntax |
| =IFS(logical_test1,value_if_true1,[logicaltest2,value_if_true2]...[logical_test127,value_if_true127]) |
| Purpose / Description |
| Returns a value for the first condition that is met. Allows for complex logical statements without the need to nest IF functions, one-inside-another. |
| Arguments |
| Name |
Type |
Description |
| logical_test1 |
logical |
A constant, reference or nested expression that indicates whether the first condition has been met. Values other than logical values will be coerced to a logical in possible. |
| value_if_true1 |
any |
A constant, reference or nested expression that provides the result to be returned if logical_test1 is TRUE. |
| {logical_test2]... |
logical |
Optional constants, references or nested expressions indicating whether further alternative conditions are met. For each extra logical_testn, there must be a value_if_truen. |
| [value_if_true2]... |
any |
Optional constants, references or nested expressions that provide the result to be returned if logical_testn is TRUE. |
| Return Values |
| Type |
Specific Value |
Description |
| any |
|
The value of the value_if_truen for the first logical_testn that is TRUE. |
| error |
#N/A |
If none of the given logical tests is TRUE, i.e. no conditions are met. |
| 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 mutually exclusive conditions. |
| Note: |
This function requires an even number of arguments to be provided. One value_if_truen for each logical_testn. |
| Advice: |
To avoid #N/A errors, we recommend adding a final default case, with the logical_test as TRUE, and the default answer. For example: =IFS(A1>0,somevalue,A1<-10000,someothervalue,TRUE,defaultanswer) |
| Warning: |
The order in which conditions is listed is important. If two conditions are met, the result is that associated with the first condition that is met. |
| Examples: |
Open / download sample workbook: IFS.xlsx |
| |