IF is the function that lets Excel make decisions. Combine it with AND and OR, and you can test several conditions at once, like “bonus if sales are over 10,000 and the employee has been here a year.”
The IF function
=IF(logical_test, value_if_true, value_if_false)Example: show “Pass” if the score in B2 is 50 or more, otherwise “Fail”.
=IF(B2>=50, "Pass", "Fail")Text results must be in quotation marks. Numbers and formulas don’t need them:
=IF(B2>10000, B2*5%, 0)Comparison operators
| Operator | Meaning | Example |
|---|---|---|
| = | Equal to | A2=”Paid” |
| <> | Not equal to | A2<>”Paid” |
| > | Greater than | B2>100 |
| < | Less than | B2<100 |
| >= | Greater than or equal to | B2>=50 |
| <= | Less than or equal to | B2<=50 |
The AND function
AND returns TRUE only if all conditions are true.
=AND(B2>10000, C2>=1)The OR function
OR returns TRUE if at least one condition is true.
=OR(D2="Manager", D2="Director")IF with AND
Put AND inside the logical test of IF. Example: give a bonus if sales (B2) are over 10,000 and years of service (C2) is at least 1.
=IF(AND(B2>10000, C2>=1), "Bonus", "No bonus")| Sales | Years | Result |
|---|---|---|
| 12,000 | 2 | Bonus |
| 12,000 | 0 | No bonus |
| 8,000 | 3 | No bonus |
IF with OR
Example: free shipping if the order (B2) is over 100 or the customer type (C2) is “VIP”.
=IF(OR(B2>100, C2="VIP"), "Free", "Paid")IF with AND and OR together
Example: approve a request if the amount is under 500 and the department is either Sales or Marketing.
=IF(AND(B2<500, OR(C2="Sales", C2="Marketing")), "Approved", "Review")Build complex tests step by step: write the AND or OR part in its own cell first, check it returns TRUE and FALSE correctly, then wrap it in IF.
Nested IF (more than two outcomes)
To return more than two results, put an IF inside another IF. Example: grades from scores.
=IF(B2>=85, "A", IF(B2>=75, "B", IF(B2>=65, "C", "F")))In Excel 365, 2019 and later, IFS is easier to read:
=IFS(B2>=85, "A", B2>=75, "B", B2>=65, "C", TRUE, "F")The final TRUE, "F" acts as the “everything else” result.
Practical examples
Overdue invoice: due date in C2, status in D2.
=IF(AND(C2<TODAY(), D2<>"Paid"), "Overdue", "")Check if a cell is empty:
=IF(A2="", "Missing", "OK")Stock reorder alert: stock in B2, minimum in C2.
=IF(B2<=C2, "Reorder", "")Common mistakes
- Missing quotes around text:
=IF(A2=Paid, ...)gives #NAME?. Use"Paid". - Writing conditions like maths:
=IF(10<B2<20, ...)doesn’t work. UseAND(B2>10, B2<20). - Numbers in quotes:
B2>"100"compares to text. Remove the quotes. - Too many nested IFs: beyond three or four levels, use IFS or a lookup table with XLOOKUP instead.
Highlight results with colour
The same AND and OR logic works in conditional formatting, so you can colour overdue invoices red automatically. See 10 conditional formatting examples.
Want to add up values that meet conditions instead? Read SUMIFS and COUNTIFS Explained.