IFS
Checks several conditions in turn and returns the answer for the first one that’s met: a customer tier, a grade, a discount size.
IFS(logical_test1, value_if_true1, ...)- logical_test1
- The first condition, for example B2>=500000.
- value_if_true1
- What to return if the first condition is met. Then come the second pair, the third and so on, in the same way.
Return value
The value for the first condition that’s met. If none is met, you get a #N/A error.
Examples
Customer tier by total purchases
fx
A
B
C
D
1
2
3
4
5
6
7
8
Customer
Purchases, $
Tier
Vector Inc
620000
Sokol Design
240000
Sphere LLC
85000
Nord Corp
500000
Lee Studio
199000
Sheet1
Details
IFS replaces a chain of nested IFs. The arguments come in pairs: a condition and its answer. Excel checks the pairs from left to right and stops at the first condition that’s met.
The order of conditions matters: start with the strictest. To set an answer for “everything else”, write TRUE as the last condition.
The function is available in Excel 2019, 2021 and Microsoft 365. In older versions, use nested IFs instead.
Common mistakes
- The loose condition comes first: B2>=200000 also catches those with 600,000, and Gold is never reached.
- No fallback answer: if no condition is met, IFS returns #N/A. Add the pair TRUE,"Bronze" at the end.
- An odd number of arguments: every condition needs its own answer.