IFS - Assign the first matching outcome
Map ordered conditions to performance bands while handling overlapping thresholds and a default outcome.
By the end, you can
- Pair each test with its result.
- Order overlapping thresholds correctly.
- Use a final TRUE test as a fallback.
Before you start: Understand IF comparisons. AND is useful for the linked practice challenge.
In this lesson
More than two outcomes
IFS checks conditions in order and returns the result paired with the first TRUE test. A training team can map scores to Outstanding, On track, or Coaching without nesting several IF functions. Once a test matches, a later matching test does not replace its result.
Syntax and ordered pairs
=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)Each logical test needs its own result. Excel supports up to 127 condition/result pairs, but keep practical rules short and reviewable.
| Argument | Purpose | Example |
|---|---|---|
logical_test1, value_if_true1Required | The first condition and the result it returns when TRUE. | B2>=90, "Outstanding" |
additional pairsOptional | Later tests and results, evaluated in their written order. | B2>=75, "On track" |
TRUE, fallbackOptional | An optional final always-true condition and default result. | TRUE, "Coaching" |
Performance bands from one score
Outstanding| A | B | C | |
|---|---|---|---|
| 1 | Employee | Score | Band |
| 2 | Anika | 94 | Outstanding |
| 3 | Dev | 75 | On track |
| 4 | Meera | 68 | Coaching |
| 5 | Rahul | 88 | On track |
=IFS(B2>=90,"Outstanding",B2>=75,"On track",TRUE,"Coaching")94 meets the first threshold, so the result is Outstanding even though it also meets >=75.
Inspect boundaries and return numbers
=IFS(B3>=90,"Outstanding",B3>=75,"On track",TRUE,"Coaching")Dev fails the first test but passes the second, including exactly 75.
=IFS(B4>=90,"Outstanding",B4>=75,"On track",TRUE,"Coaching")Meera's 68 fails both thresholds; the final TRUE condition matches.
=IFS(B5>=90,1000,B5>=75,500,TRUE,0)For awards of ₹1,000, ₹500, or zero using the same bands, Rahul's 88 earns the middle amount.
First match and compatibility
- Conditions can overlap. Only the first matching result is returned, so order is part of the policy.
- If no test is TRUE and no fallback is supplied, IFS returns #N/A.
- A test without a paired result is an incomplete formula. Errors in conditions need investigation; a fallback is not an error handler.
- IFS is available in Excel 2019 and later and Microsoft 365. Older Excel versions may need nested IF instead.
Common mistakes and fixes
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.