IFS Function: Modern Multiple Conditions (2025)
Quick Answer: IFS tests multiple conditions: =IFS(A1>=90,"A", A1>=80,"B", A1>=70,"C", TRUE,"F"). Returns first TRUE result. Replaces nested IF. Excel 2019/365 only. Use TRUE as last test for default value.
What is IFS?
IFS is Excel's modern replacement for nested IF statements. It tests multiple conditions sequentially and returns the value for the first TRUE condition. Clean, readable, powerful.
Nested IF vs IFS:
Old Way: Nested IF
=IF(A1>=90,"A",
IF(A1>=80,"B",
IF(A1>=70,"C",
"F")))Hard to read, easy to break
New Way: IFS
=IFS( A1>=90,"A", A1>=80,"B", A1>=70,"C", TRUE,"F")
Clean, sequential, readable
How IFS Works:
Tests first condition
If TRUE, returns associated value
If FALSE, moves to next
Tests second condition
Continues sequentially
Until it finds TRUE or runs out
No TRUE found?
Returns #N/A error (use TRUE as catch-all)
Key Advantages:
No Nesting
Flat structure, easy to read and maintain
Up to 127 Conditions
Much more than nested IF can handle
Easier Debugging
See exactly which condition failed
Excel 2019/365 Only
Not available in earlier versions
💡 Common Use Cases
- • Grading systems: Convert scores to letter grades
- • Pricing tiers: Assign prices based on quantity ranges
- • Status assignments: Categorize by multiple thresholds
- • Performance ratings: Rate employees/products by criteria
- • Tax brackets: Calculate rates based on income levels
IFS Syntax
=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)
Arguments (Paired)
IFS requires pairs of test and value. Minimum 1 pair, maximum 127 pairs.
logical_test1 (required)
First condition to test (must evaluate to TRUE/FALSE)
A1>=90 or B2="Complete"value_if_true1 (required)
Value to return if test1 is TRUE
"A" or 100 or C5*2logical_test2, value_if_true2... (optional)
Additional test/value pairs (up to 127 pairs total)
A1>=80, "B", A1>=70, "C"Common Patterns
Grading System
=IFS(A1>=90,"A", A1>=80,"B", A1>=70,"C", A1>=60,"D", TRUE,"F")
Price Tiers
=IFS(A1>=100, 10, A1>=50, 15, A1>=10, 20, TRUE, 25)
Status Categories
=IFS(A1>1000,"High", A1>500,"Medium", A1>0,"Low", TRUE,"None")
Multiple Criteria
=IFS(AND(A1>90,B1="Yes"),"Excellent", A1>80,"Good", TRUE,"Fair")
⚠️ Important Notes
- • Sequential evaluation: IFS stops at first TRUE (order matters!)
- • Default value: Use TRUE as last test for catch-all:
TRUE, "Default" - • No TRUE found: Returns #N/A error if no condition is TRUE
- • Version requirement: Excel 2019, Excel 365, Excel for Mac 2019+
- • Cell ranges: Tests must return TRUE/FALSE (use comparison operators)
- • Performance: IFS is efficient even with many conditions
🚨 Common Mistake
❌ Wrong Order:
=IFS(A1>0,"Positive", A1>100,"High") // A1>100 never triggers!
✓ Correct Order:
=IFS(A1>100,"High", A1>0,"Positive") // Most specific first
Always put most restrictive conditions first!
10+ IFS Examples
Educational Grading
1. Letter Grades
=IFS(A2>=90,"A", A2>=80,"B", A2>=70,"C", A2>=60,"D", TRUE,"F")
Standard grading scale with F as default
2. Plus/Minus Grades
=IFS(A2>=97,"A+", A2>=93,"A", A2>=90,"A-", A2>=87,"B+", A2>=83,"B", TRUE,"B-")
Detailed grading with + and - modifiers
3. Pass/Fail with Honors
=IFS(A2>=95,"Pass with Honors", A2>=70,"Pass", TRUE,"Fail")
Three-tier pass/fail system
Business & Pricing
4. Quantity Discounts
=IFS(A2>=100, A2*8, A2>=50, A2*10, A2>=10, A2*12, TRUE, A2*15)
Price per unit decreases with quantity
5. Commission Tiers
=IFS(A2>50000, A2*0.15, A2>25000, A2*0.10, A2>10000, A2*0.05, TRUE, 0)
Sales commission based on revenue
6. Shipping Costs
=IFS(A2>=100, 0, A2>=50, 5, A2>=25, 10, TRUE, 15)
Free shipping over $100, tiered costs below
Performance & Status
7. Employee Performance
=IFS(A2>95,"Outstanding", A2>85,"Exceeds", A2>70,"Meets", TRUE,"Needs Improvement")
Four-tier performance rating
8. Project Status
=IFS(A2=100,"Complete", A2>=75,"On Track", A2>=50,"At Risk", TRUE,"Behind")
Progress percentage to status
9. Priority Levels
=IFS(AND(A2="Critical",B2<7),"Urgent", A2="High","Important", TRUE,"Normal")
Combining severity and timeline
Advanced Techniques
10. Nested AND/OR
=IFS(AND(A2>90,B2>90),"Both High", OR(A2>90,B2>90),"One High", TRUE,"Both Low")
Complex logical combinations
11. Date-Based Status
=IFS(A2<TODAY(),"Overdue", A2=TODAY(),"Due Today", A2<=TODAY()+7,"Due Soon", TRUE,"Future")
Categorize by due date
12. Text Pattern Matching
=IFS(LEFT(A2,3)="EMP","Employee", LEFT(A2,3)="CON","Contractor", TRUE,"Unknown")
Categorize by ID prefix
🎯 Pro Tips
- • Order matters: Most specific conditions first, broadest last
- • Always use TRUE: Last test should be TRUE for default value
- • Debug strategy: Test each condition separately with IF first
- • Backwards compatibility: Use nested IF for Excel 2016 and earlier
- • Combine with functions: Use AND, OR, TEXT, LEFT, etc. in tests
Frequently Asked Questions
Can I use IFS in Excel 2016 or earlier?
No. IFS requires Excel 2019, Excel 365, or Excel for Mac 2019+. For older versions, use nested IF: =IF(test1,value1,IF(test2,value2,default)). Or consider upgrading to Excel 365 for modern functions like IFS, XLOOKUP, FILTER.
What happens if no condition is TRUE?
IFS returns #N/A error. Always add TRUE as your last test for a default value: =IFS(A1>90,'A', A1>80,'B', TRUE,'F'). The TRUE condition always evaluates to TRUE, catching all remaining cases.
Should I always use IFS instead of nested IF?
Use IFS for 3+ conditions - it's cleaner and more readable. For 1-2 conditions, regular IF is fine: =IF(A1>100,'High','Low'). IFS shines with many conditions. But check Excel version compatibility first.
Can I use VLOOKUP or other functions in IFS tests?
Yes! IFS accepts any logical test. =IFS(VLOOKUP(A1,Table,2,0)>100,'High', ISNA(VLOOKUP(A1,Table,2,0)),'Not Found', TRUE,'Low'). Just ensure the test returns TRUE/FALSE. Use IFERROR to handle errors.
Why is my IFS not working correctly?
Common issues: (1) Wrong order - most specific tests must come first. (2) Missing TRUE default - add TRUE as last test. (3) Test doesn't return TRUE/FALSE - use comparison operators. (4) Excel version - IFS needs 2019+. (5) Odd number of arguments - must be test/value pairs.
How is IFS different from SWITCH?
IFS: Tests conditions sequentially (>, <, AND). =IFS(A1>90,'A', A1>80,'B'). SWITCH: Matches exact values. =SWITCH(A1, 'Red',1, 'Blue',2, 3). Use IFS for ranges/conditions, SWITCH for exact matching. IFS is more flexible for most scenarios.