Excel IFS Function: Test Several Conditions Without Nesting
Checks conditions in order and returns the result of the first one that is true.
IFS is what a nested IF should have looked like all along. Instead of burying each new condition inside the false branch of the last one, you list them flat: condition, result, condition, result, for as many pairs as you need. The first condition that returns TRUE wins and nothing after it is evaluated.
That last part is the rule that catches people. IFS stops at the first match, so the order you write the conditions in *is* the logic. Testing B2>=50 before B2>=70 means every score above 70 is caught by the 50 test first and never reaches the one you meant. Conditions go from most restrictive to least, always.
There is no built-in default. If nothing matches you get #N/A, and the standard fix is a final pair whose condition is simply TRUE — it always matches, so it acts as the else.
Syntax
=IFS(condition1, value1, [condition2, value2], …)Arguments
condition1Required- The first test. Anything evaluating to TRUE or FALSE.
value1Required- What to return when condition1 is TRUE.
condition2, value2, …Optional- Further pairs, up to 127. They are checked in order and the first TRUE one wins.
The example data
Headers in row 1, data in A2:D5.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Student | Score | Attendance | Course |
| 2 | Alice Moreau | 82 | 0.96 | Finance |
| 3 | Bruno Santos | 54 | 0.71 | Marketing |
| 4 | Chen Wei | 91 | 0.88 | Finance |
| 5 | Dana Okafor | 67 | 0.62 | Operations |
Worked examples
=IFS(B2>=90, "A", B2>=80, "B", B2>=70, "C", TRUE, "Fail")Result: B
Alice's 82 fails the first test and passes the second. The final TRUE is the default.
=IFS(B4>=90, "A", B4>=80, "B", B4>=70, "C", TRUE, "Fail")Result: A
Chen's 91 matches on the first condition, so nothing else is checked.
=IFS(B3>=90, "A", B3>=80, "B", B3>=70, "C")Result: #N/A
Bruno's 54 matches nothing and there is no default, so IFS reports that it found no answer.
=IFS(AND(B2>=70, C2>=0.8), "Distinction", B2>=70, "Pass", TRUE, "Refer")Result: Distinction
Conditions can be as complex as you like. The most demanding one has to come first.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
#N/A
Why it happens: No condition matched and there is no default pair.
How to fix it: Add TRUE as the last condition with your fallback value: …, TRUE, "Other").
Everything returns the same result
Why it happens: The conditions are in the wrong order, so a broad one catches every row before a narrow one is reached.
How to fix it: Order from most restrictive to least. With numeric bands that means descending thresholds.
#VALUE!
Why it happens: An odd number of arguments — a condition with no matching value.
How to fix it: Count them in pairs. IFS never takes a lone final value the way IF does.
#NAME?
Why it happens: The Excel version predates IFS.
How to fix it: IFS needs Excel 2019 or Microsoft 365. Older versions need nested IFs.
Tips worth knowing
- TRUE as the final condition is how you write an else. It is a convention, not a special feature.
- For banding numbers, a small lookup table with VLOOKUP's approximate match is often easier to maintain than either IFS or nested IFs, because the thresholds become data.
- SWITCH is clearer than IFS when every condition compares the same value to a different constant.
- Keep each result short. If the values are themselves formulas, the flat list stops being readable.
Frequently asked questions
How do I add an else to IFS?
Make the last condition the literal TRUE and pair it with your fallback: =IFS(A2>90, "A", A2>80, "B", TRUE, "Other"). TRUE always matches, so it runs whenever nothing above it did.
Why does my IFS return the wrong band?
The conditions are almost certainly in ascending order. IFS returns the first match, so a test of >=50 written before >=70 catches every high score too. Reorder them from the most restrictive threshold down.
Is IFS better than nested IFs?
For readability, yes — the conditions sit at the same level instead of burrowing into each other, and there is no closing-bracket pile-up. They behave identically otherwise. IFS needs Excel 2019 or later, so a workbook that must open in older versions still needs nesting.
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.