Description
The Excel IFS function is a logical function in Excel that allows you to perform multiple logical tests and returns a value that corresponds to the first TRUE result. IFS is a powerful alternative to nested IF statements as it is shorter, easier to read, and has a higher limit for number of conditions to check.
Syntax
IFS( logical_test1, value_if_true1, [logical_test2, value_if_true2], …)
Arguments
logical_test1 – The first condition that you want to test
value_if_true1 – The result you want to be returned if the logical_test1 result is TRUE
logical_test2 – [Optional] The second condition that you want to test
value_if_true2 – [Optional] The result you want to be returned if the logical_test2 result is TRUE
Return value
Value that corresponds to the first TRUE result
How to use the IFS function in Excel
IFS is a function to test multiple conditions and return a value that corresponds to the first TRUE result. It eliminates the need to use nested IF functions for checking multiple conditions and allows you to produce formulas with many arguments that are easy to read.
The arguments for the IFS function are entered incrementally and pairwise as test/ value pairs. Each logical test returns either TRUE or FALSE, and in case it returns TRUE the respective value is returned
In the above example, we want to assign the correct salary bonus for each employee. As there multiple bonus levels, we perform multiple logical tests in a row until the correct bonus level is found. The IFS statement used above looks like this:
= IFS(C6>90,15000, C6>70,7000, C6>50,3000, C6>30,1000, C6<30,0)
Note, that we explicitly covered the last case of C6 < 30. We did that as the IFS function does not allow to define a custom return value if none of the logical tests evaluates TRUE (like we are used to from the IF function). For the IFS function we have to explicitly define this alternative conditions. Otherwise, if none of the logical tests in the IFS function evaluates TRUE, a #N/A error is returned.
In case you don’t want to explicitly define the condition that covers all other cases, we recommend to embed the IFS function in a IFNA function. IFNA catches the #N/A error in case none of the conditions in the IFS statement are TRUE and allows you define a default return value without explicitly defining the respective condition. For the above example, the adjusted formula would look like this:
Alternatively, you can also enter TRUE for the last logical test, and then a value to return as a default if all other logical tests evaluate FALSE:
= IFS(C6>90,15000, C6>70,7000, C6>50,3000, C6>30,1000, TRUE,0)
However, we think this version is less intuitive to understand for a third person.
Logical Operators
Here is an overview of all logical operators you can use to construct the logical tests inside of IFS:
Logical Operator | Meaning | Example |
|---|---|---|
= | equal to | A1 = C1 |
<> | not equal to | A1 <> C1 |
> | greater than | A1 > C1 |
< | smaller than | A1 < C1 |
>= | greater than or equal to | A1 >= C1 |
<= | smaller than or equal to | A1 <= C1 |
Alternative functions to replace nested IF statements
Even though the IFS function is a good alternative for nested IF statements, we recommend to also take a look at the following functions if you want to multiple, mutually exclusive conditions:
AND formula examples
Related Tutorials
Related Functions
Excel SWITCH function
The Excel SWITCH compares one value against a list of values, and returns a result value corresponding to the first match.
Excel IFNA function
The Excel IFNA function is a logical function to trap and handle the #N/A error type in a formula. It returns a custom result for a #N/A error.
Excel IF function
The Excel IF function allows you to perform a simple logical test and returns one value for a TRUE result, and another value for a FALSE result.
