Logical
How to Use IFS in Excel (Cleaner Multi-Condition Logic)
The IFS function checks several conditions in order and returns the value for the first one that is true. It is the modern, readable replacement for stacking many IF functions inside each other.
This guide covers the exact syntax, real worked examples for grades and bands, and how to add a catch-all "else" so an unmatched value never returns #N/A.
IFS syntax
=IFS(logical_test1, value1, [logical_test2, value2], ...) | Argument | Required | What it does |
|---|---|---|
logical_test1 | Required | The first condition to test, e.g. B2>=90. Conditions are checked top to bottom. |
value1 | Required | What to return when logical_test1 is the first TRUE condition. |
logical_test2 | Optional | A second condition, checked only if the first was FALSE. |
value2 | Optional | The value for logical_test2. Add as many condition/value pairs as you need. |
Note: IFS stops at the first TRUE test. Add TRUE as the final condition for a catch-all "else". Available in Excel 2019, Microsoft 365, and Google Sheets.
Examples
Real, copy-paste-ready formulas.
1. Letter grades from a score
=IFS(B2>=90,"A", B2>=80,"B", B2>=70,"C", TRUE,"F") IFS checks each condition in order and returns the value for the first TRUE one. The final TRUE,"F" is the catch-all "else" for anything below 70.
2. Shipping bands by weight
=IFS(C2<1,"Light", C2<5,"Medium", TRUE,"Heavy") Order matters: a 0.5 kg parcel matches C2<1 first and never reaches the later tests, so list bands from smallest to largest here.
3. Commission rate by sales
=IFS(D2>=100000,0.1, D2>=50000,0.07, D2>=10000,0.05, TRUE,0) IFS returns numbers as easily as text. List the highest threshold first so larger values are caught before the smaller bands.
4. Map codes with a default
=IFS(E2="A","Active", E2="C","Closed", E2="P","Pending", TRUE,"Unknown") Without the final TRUE, a code that matches nothing returns #N/A. TRUE,"Unknown" guarantees a value for every input.
How to write IFS step by step
- 1
Click the result cell and type =IFS(
- 2
Enter the first condition, e.g. B2>=90, then a comma.
- 3
Type the value to return when that condition is true, then a comma.
- 4
Add more condition, value pairs in priority order (highest threshold first).
- 5
Finish with TRUE, then a default value as the catch-all "else".
- 6
Close the bracket ) and press Enter.
Common errors and fixes
| Error | Why it happens | How to fix it |
|---|---|---|
#N/A | No condition was TRUE and there is no catch-all, so IFS has nothing to return. | Add TRUE as the last condition followed by a default value, e.g. TRUE,"Other". |
Wrong band returned | Conditions are listed in the wrong order, so a value matched an earlier, broader test first. | Order tests from most specific to least, typically highest threshold first. |
#NAME? | IFS is not available in this version (Excel 2016 or earlier). | Use nested IF or SWITCH instead, or upgrade to Excel 2019 or Microsoft 365. |
You have entered too few arguments | A condition was added without its matching value, leaving an odd number of arguments. | Check that every condition is followed by exactly one return value. |
Frequently asked questions
What is the difference between IFS and nested IF?
IFS lists conditions as flat condition/value pairs, so it is far easier to read than several IFs nested inside each other. Both give the same result; IFS just avoids the pile of closing brackets.
How do I add an "else" in IFS?
There is no dedicated else argument. Add TRUE as the final condition followed by your default value, e.g. =IFS(..., TRUE,"Other"). TRUE is always true, so it catches anything the earlier tests missed.
Why does my IFS return #N/A?
IFS returns #N/A when none of the conditions are TRUE. Add a final TRUE, default pair as a catch-all so every input gets a value.
Does IFS work in Google Sheets?
Yes, IFS works identically in Google Sheets and in Excel 2019 and Microsoft 365. Excel 2016 and earlier do not have it; use nested IF there.