Basic IF
Syntax
=IF(condition, value_if_true, value_if_false)
Pass/Fail based on score
=IF(B2 >= 70, "Pass", "Fail")
Check if a cell is empty
=IF(A2 = "", "Missing", "Has data")
Return a calculation or zero
=IF(B2 > 0, A2/B2, 0)
Nested IF (Multiple Conditions)
When you need more than two outcomes, nest IF statements inside each other:
Letter grade from score
=IF(B2 >= 90, "A",
IF(B2 >= 80, "B",
IF(B2 >= 70, "C",
IF(B2 >= 60, "D", "F")
)
)
)
Priority label from amount
=IF(C2 >= 10000, "Critical",
IF(C2 >= 5000, "High",
IF(C2 >= 1000, "Medium", "Low")
)
)
Nested IFs work, but they get unreadable fast. After 3 levels, use IFS instead.
IFS: The Cleaner Alternative
IFS checks multiple conditions in order and returns the first match. No nesting required.
Same letter grade, much cleaner
=IFS(
B2 >= 90, "A",
B2 >= 80, "B",
B2 >= 70, "C",
B2 >= 60, "D",
TRUE, "F"
)
The TRUE at the end acts as "else", the default case if nothing else matches.
Shipping cost by weight
=IFS(
D2 <= 1, 5.99,
D2 <= 5, 9.99,
D2 <= 20, 14.99,
TRUE, 24.99
)
IF with AND: Multiple Conditions Must All Be True
Bonus if sales > $10K AND rating >= 4
=IF(AND(C2 > 10000, D2 >= 4), "Bonus", "No bonus")
Eligible if age 18-65 AND status is Active
=IF(AND(B2 >= 18, B2 <= 65, C2 = "Active"),
"Eligible", "Not eligible")
IF with OR: At Least One Condition Must Be True
Flag if overdue OR over budget
=IF(OR(D2 < TODAY(), E2 > F2),
"Needs attention", "On track")
Discount for VIP or wholesale customers
=IF(OR(B2 = "VIP", B2 = "Wholesale"),
C2 * 0.85, C2)
SWITCH: Match Exact Values
When you're matching exact values (not ranges), SWITCH is cleaner than IF:
Department code to full name
=SWITCH(A2,
"ENG", "Engineering",
"MKT", "Marketing",
"SAL", "Sales",
"OPS", "Operations",
"Unknown"
)
Day number to day name
=SWITCH(WEEKDAY(A2),
1, "Sunday",
2, "Monday",
3, "Tuesday",
4, "Wednesday",
5, "Thursday",
6, "Friday",
7, "Saturday"
)
Real-World Examples
Commission calculator
Tiered commission rates
=IFS(
C2 >= 100000, C2 * 0.12,
C2 >= 50000, C2 * 0.10,
C2 >= 25000, C2 * 0.08,
C2 >= 10000, C2 * 0.06,
TRUE, C2 * 0.04
)
Invoice status from due date
Auto-status based on due date and payment
=IF(E2 = "Paid", "Paid",
IF(D2 < TODAY(), "Overdue",
IF(D2 - TODAY() <= 7, "Due Soon", "Current")
)
)
Lead scoring
Score leads by multiple factors
=IF(AND(B2 = "Enterprise", C2 >= 3, D2 = "Demo Requested"),
"Hot",
IF(OR(B2 = "Enterprise", C2 >= 5),
"Warm", "Cold"
)
)
When to Use What
- IF: Two outcomes (yes/no, pass/fail)
- Nested IF: 3-4 outcomes based on ranges (but consider IFS instead)
- IFS: 3+ outcomes based on ranges, cleaner than nested IF
- AND/OR inside IF: Multiple conditions must (AND) or can (OR) be true
- SWITCH: Matching exact values (codes, categories, statuses)
If you're nesting more than 3 IFs deep, switch to IFS. Your future self will thank you.