IFS
LogicalintermediateIFS replaces nested IF statements. Instead of writing =IF(A, x, IF(B, y, IF(C, z, default))), you write =IFS(A, x, B, y, C, z, TRUE, default). It's more readable and less error-prone for multi-condition logic. Available in Excel 2019+ and Google Sheets.
Syntax
=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)Parameters
logical_test1The first condition to evaluate.value_if_true1The value to return if the first condition is true.logical_test2, value_if_true2?Additional condition/value pairs. Use TRUE as the final test for a default/catch-all.? = optional parameter
Worked Example
Categorize invoices: over $20k = 'Enterprise', over $5k = 'Mid-Market', everything else = 'Small'.
| Row numbers | E | |
|---|---|---|
| Header row | amount | tier |
| 2 | $33,000.00 | Enterprise |
| 3 | $12,500.00 | Mid-Market |
| 4 | $7,600.00 | Mid-Market |
| 5 | $3,200.00 | Small |
| 6 | $487.50 | Small |
Formula
=IFS(A2>20000, "Enterprise", A2>5000, "Mid-Market", TRUE, "Small")Result
EnterpriseIFS checks the amount in A2 against each threshold in order. Because A2 is $33,000.00, the first test is true and the formula returns 'Enterprise' without evaluating the later labels.
Practice Exercises
2 exercises - work through each one using the data table below
Question
Categorize each invoice's amount into tiers: over $20,000 = 'Tier 1', over $10,000 = 'Tier 2', over $5,000 = 'Tier 3', everything else = 'Tier 4'. What tier is invoice INV-1003 ($18,750)?
Dataset
Showing rows 1-10
Download CSV| Row numbers | A | B | C |
|---|---|---|---|
| Header row | |||
| 2 | INV-1001 | Cloudline Software | $12,500.00 |
| 3 | INV-1002 | Apex Office Supply | $487.50 |
| 4 | INV-1003 | Summit Consulting Group | $18,750.00 |
| 5 | INV-1004 | Meridian Travel Co. | $3,200.00 |
| 6 | INV-1005 | Forge & Frame Creative | $7,600.00 |
| 7 | INV-1006 | NovaTech Solutions | $9,800.00 |
| 8 | INV-1007 | Atlas Hardware Co. | $2,150.00 |
| 9 | INV-1008 | Clearview Data Systems | $15,300.00 |
| 10 | INV-1009 | Redstone Marketing | $4,250.00 |
| 11 | INV-1010 | Keystone Legal Advisors | $8,900.00 |
| 12 |
Scroll to explore the table, then sort or filter by column as needed.
Hints
Your Answer
Question
Create a payment speed label: if payment_date is within 14 days of invoice_date = 'Fast', within 30 days = 'Normal', over 30 days = 'Slow'. For unpaid invoices, it should say 'Unpaid'. What label would INV-1001 get? (Invoice date: Jan 15, Payment date: Feb 1 = 17 days)
Dataset
Showing rows 1-5
Download CSV| Row numbers | A | B | C | D | E |
|---|---|---|---|---|---|
| Header row | |||||
| 2 | INV-1001 | Cloudline Software | 2025-01-15 | Paid | 2025-02-01 |
| 3 | INV-1002 | Apex Office Supply | 2025-01-18 | Paid | 2025-01-25 |
| 4 | INV-1003 | Summit Consulting Group | 2025-01-22 | Paid | 2025-02-10 |
| 5 | INV-1004 | Meridian Travel Co. | 2025-02-03 | Paid | 2025-02-15 |
| 6 | INV-1005 | Forge & Frame Creative | 2025-02-08 | Paid | 2025-02-20 |
| 7 |
Scroll to explore the table, then sort or filter by column as needed.
Hints
Your Answer
Want more practice?
Test Me mode randomizes exercises across all formulas without telling you which formula to use.