Excel IF Function: Complete Guide with Examples (2025)
Quick Answer: IF tests conditions: =IF(condition, value_if_true, value_if_false). Example: =IF(A1>100,"High","Low"). Nested IF: =IF(A1>=90,"A",IF(A1>=80,"B","C")). With AND: =IF(AND(A1>10,B1<5),"Yes","No"). Use IFS for 3+ conditions in Excel 2019+.
What is the IF Function?
The IF function is Excel's most popular logical function. It tests a condition and returns one value if TRUE, another if FALSE. Think of it as asking a yes/no question in Excel.
Simple Example:
=IF(A1 > 100, "High", "Low") // If A1 is greater than 100, return "High" // Otherwise, return "Low"
Condition
Test: A1 > 100
The question you're asking
If TRUE
Return: "High"
What happens if condition is met
If FALSE
Return: "Low"
What happens if condition is not met
💡 Common Use Cases
- • Pass/Fail grading: IF(score>=60,"Pass","Fail")
- • Bonus calculations: IF(sales>10000,sales*0.1,0)
- • Status indicators: IF(deadline<TODAY(),"Overdue","On Time")
- • Conditional formatting data: IF(inventory<10,"Order","OK")
- • Data validation: IF(ISBLANK(A1),"Missing","Complete")
IF Function Syntax
=IF(logical_test, value_if_true, value_if_false)
1. logical_test (required)
The condition to evaluate. Returns TRUE or FALSE.
A1 > 100 ← Greater thanB2 = "Complete" ← Equal to textC3 <= TODAY() ← Compare with today's date2. value_if_true (required)
What to return if condition is TRUE.
"Pass" ← Return textA1 * 1.1 ← Return calculationB1 ← Return cell value3. value_if_false (required)
What to return if condition is FALSE.
"Fail" ← Return text0 ← Return zero"" ← Return empty (blank)Comparison Operators
=Equal to>Greater than<Less than
>=Greater than or equal<=Less than or equal<>Not equal to
📌 Important Notes
- • Text must be in "quotes"
- • Comparisons are case-insensitive: "APPLE" = "apple"
- • Empty cells are treated as 0 in numeric comparisons
- • TRUE = 1, FALSE = 0 in calculations
15+ IF Function Examples
1. Simple Pass/Fail
=IF(A2 >= 60, "Pass", "Fail")
Returns "Pass" if score is 60 or higher, otherwise "Fail"
2. Bonus Calculation
=IF(B2 > 10000, B2 * 0.1, 0)
10% bonus if sales exceed $10,000, otherwise zero
3. Blank Cell Check
=IF(A2 = "", "Missing", "Complete")
Shows "Missing" for blank cells, "Complete" otherwise
Nested IF (Multiple Conditions)
4. Letter Grading System
=IF(A2 >= 90, "A",
IF(A2 >= 80, "B",
IF(A2 >= 70, "C",
IF(A2 >= 60, "D", "F"))))A for 90+, B for 80-89, C for 70-79, D for 60-69, F below 60
5. IFS Alternative (Excel 2019+)
=IFS(A2>=90,"A", A2>=80,"B", A2>=70,"C", A2>=60,"D", TRUE,"F")
Cleaner syntax for multiple conditions in modern Excel
IF with AND (All Conditions Must Be True)
6. Two Conditions (AND)
=IF(AND(A2 > 50, B2 = "Active"), "Qualified", "Not Qualified")
Returns "Qualified" only if value > 50 AND status is "Active"
7. Three Conditions (AND)
=IF(AND(A2>=18, B2="USA", C2="Yes"), "Eligible", "Not Eligible")
All three conditions must be TRUE to return "Eligible"
IF with OR (Any Condition Can Be True)
8. Two Conditions (OR)
=IF(OR(A2 = "Urgent", B2 = "High"), "Priority", "Normal")
Returns "Priority" if EITHER condition is TRUE
9. Weekend Checker
=IF(OR(WEEKDAY(A2)=1, WEEKDAY(A2)=7), "Weekend", "Weekday")
Returns "Weekend" if date is Saturday or Sunday
Advanced IF Examples
10. Date Comparison
=IF(A2 < TODAY(), "Overdue", "On Track")
11. Percentage Calculation
=IF(B2 = 0, "N/A", A2/B2)
12. Tiered Discount
=IF(A2>=1000, 0.2, IF(A2>=500, 0.15, IF(A2>=100, 0.1, 0)))
🎯 Pro Tips
- • Limit nesting: Max 7 nested IFs, use IFS or SWITCH for more
- • Performance: Put most likely conditions first
- • Readability: Use line breaks in formula bar for nested IFs
- • Error handling: Combine with IFERROR for robust formulas
Frequently Asked Questions
Can I have more than 7 nested IFs?
Excel allows up to 64 nested IFs (Excel 2007+), but formulas become unreadable after 7. Use IFS function (Excel 2019+) or SWITCH for cleaner syntax with multiple conditions. Or use VLOOKUP/XLOOKUP with a reference table.
How do I use IF with multiple AND conditions?
Combine IF with AND: =IF(AND(A1>10, B1<5, C1="Yes"), "Match", "No Match"). AND returns TRUE only if ALL conditions are TRUE. You can have up to 255 conditions in AND.
What's the difference between IF and IFS?
IF handles one condition (requires nesting for multiple). IFS (Excel 2019+) handles multiple conditions cleanly: =IFS(A1>=90,"A", A1>=80,"B", A1>=70,"C", TRUE,"F"). Use IFS when you have 3+ conditions.
Can IF return blank cells?
Yes, use empty quotes: =IF(A1>10, "High", ""). The "" returns a blank cell (not zero). This is useful for conditional displays without cluttering data.
How to avoid #DIV/0! errors with IF?
Check for zero before dividing: =IF(B1=0, "N/A", A1/B1). Or use IFERROR: =IFERROR(A1/B1, "N/A"). IF method is better for performance with large datasets.
Can I use IF for partial text matching?
Yes, combine with wildcards or functions. For contains: =IF(ISNUMBER(SEARCH("apple",A1)), "Found", "Not Found"). For starts with: =IF(LEFT(A1,3)="ABC", "Match", "No Match").