The IFS function
Several conditions, tested in order: the first true one wins. What nested IFs did with five levels of parentheses.
Syntax
=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], …)
In a French Excel: =SI.CONDITIONS(test1; valeur1; [test2; valeur2]; …).
Arguments
- logical_test1, logical_test2…: the conditions, tested in order.
- value_if_true1, value_if_true2…: the value returned when the preceding condition is the first true one.
An example
=IFS(B2>=10000,"A",B2>=5000,"B",B2>=1000,"C",TRUE,"D")
Grades a customer by revenue. The last pair, TRUE,"D", acts as the "otherwise".
Pitfalls
- No implicit "otherwise": if no condition is true, the result is #N/A. End with
TRUE,…. - Order matters:
B2>=1000placed first would also catch the 10,000 customers. - Missing from Excel 2016 and earlier.
Going further
- IF.
- Explain a formula: flags nested IFs and suggests IFS.
Frequently asked questions
- What does IFS return if no condition is true?
- The #N/A error. Add a last
TRUE,valuepair for the default case.