condition1 - Required. First logical test to evaluate (e.g., A1>=90, B2="Approved").
value1 - Required. Value to return if condition1 is TRUE.
condition2, value2 - Optional. Additional condition/value pairs. Up to 127 pairs allowed. Use TRUE as final condition for default value.
| A | B | C | |
|---|---|---|---|
| 1 | Student | Score | Grade |
| 2 | Alice | 95 | |
| 3 | Bob | 82 | |
| 4 | Carol | 73 | |
| 5 | David | 55 | =IFS(B2>=90,"A", B2>=80,"B", B2>=70,"C", B2>=60,"D", TRUE,"F") F |
Test conditions sequentially, return first match
If A1=95 → "A", if A1=85 → "B", else → "C"
Use TRUE as final condition for "else" behavior
If A1="X" → "Red", if A1="Y" → "Blue", else → "Other"
Apply different discounts based on quantity
If A1=120 → 96 (20% off), if A1=60 → 54 (10% off)
Not available in older Excel versions
Use nested IF for Excel 2016 and earlier
Convert test scores to letter grades using the IFS function in Excel. This formula makes multi-tier grading systems clean and readable compared to nested IF hell. Perfect for educators, HR managers, and performance analysts who need to categorize numeric data into discrete tiers. It eliminates the pyramid-style nesting of traditional IF statements, making grade calculations easier to audit, modify, and understand. This pattern works for any scenario requiring tiered categorization: customer segments based on purchase amounts, employee performance ratings from KPIs, product quality classifications, or risk levels from credit scores.
| A | B | C | |
|---|---|---|---|
| 1 | Student | Score | Grade |
| 2 | Alice | 95 | A |
| 3 | Bob | 82 | B |
| 4 | Carol | 73 | C |
| 5 | David | 55 | =IFS(B2>=90,"A", B2>=80,"B", B2>=70,"C", B2>=60,"D", TRUE,"F") F |
Apply different commission rates based on sales performance using the IFS function in Excel. Sales organizations typically use tiered commission structures where higher sales volumes earn higher percentage rates. This approach handles multiple tiers elegantly without complex nesting. Essential for sales managers, compensation analysts, and finance teams managing variable pay structures. It makes commission calculations transparent and easy to modify when business rules change - crucial for sales operations, partner programs, and revenue-sharing agreements across various performance thresholds.
| A | B | C | |
|---|---|---|---|
| 1 | Sales Rep | Sales | Commission Rate |
| 2 | John | $150K | 12% |
| 3 | Sarah | $85K | 10% |
| 4 | Mike | $45K | =IFS(B2>=100000,12%, B2>=75000,10%, B2>=50000,8%, TRUE,5%) 8% |
❌ The Problem:
✅ Solution:
=IFS(A1>=90,"A", A1>=80,"B", A1>=70,"C", TRUE,"F")Always include TRUE as the final condition to provide a catchall default value. TRUE is always evaluated as TRUE, so it acts like an "else" clause, ensuring the formula always returns a value instead of #N/A errors. This makes your formulas more robust and user-friendly.
❌ The Problem:
✅ Solution:
=IFS(A1>=90,"A", A1>=80,"B", A1>=70,"C", TRUE,"F")Order conditions from most specific to least specific. The formula evaluates conditions in the order you write them and returns the value for the first TRUE condition. For numeric ranges, start with the highest threshold and work down. For equality tests, put exact matches before partial matches. This ordering principle is critical for correct results.
❌ The Problem:
✅ Solution:
=IFS(A1>=90,"A", A1>=80,"B", A1>=70,"C", A1>=60,"D", TRUE,"F")Use the IFS function in Excel instead of nested IF statements for multiple conditions. It provides a flat structure where all conditions are at the same level, making it dramatically easier to read, maintain, and modify. While nested IF works, IFS is the modern best practice for multi-condition logic in Excel 2019 and Microsoft 365. Save nested IF for compatibility with older Excel versions only.
Master these IFS variants:
From basics to advanced - AI generates perfect formulas instantly.