IF is the first "smart" formula most people learn in Excel. On its own it checks one condition. Combine it with AND and OR, or nest several IFs, and it can handle grades, commissions, eligibility checks and almost any business rule.
This guide starts with the basic IF and builds up to multiple conditions, values between two numbers, nested IF, and the cleaner modern options IFS and XLOOKUP.
Quick answer: Both conditions true:=IF(AND(B2>=50, C2>=50), "Pass", "Fail"). At least one true:=IF(OR(B2="North", B2="West"), "Yes", "No"). Several outcomes: nest IFs or useIFS.
1. The basic IF
=IF(logical_test, value_if_true, value_if_false)=IF(B2>=40, "Pass", "Fail")If the mark in B2 is 40 or more, the cell shows Pass; otherwise Fail. Text results go in double quotes; numbers and cell references do not.
Comparison operators: = equal, <> not equal, >, <, >=, <=.
2. IF with AND (all conditions must be true)
=IF(AND(B2>=40, C2>=40, D2>=40), "Pass", "Fail")A student passes only if all three subjects are 40 or more. AND accepts up to 255 conditions.
3. IF with OR (at least one condition true)
=IF(OR(E2="North", E2="West"), "Zone A", "Zone B")Returns Zone A for North or West, Zone B for everything else.
4. AND and OR together
=IF(AND(OR(E2="North", E2="West"), F2>=50000), "Bonus", "No bonus")Bonus only for North or West reps with sales of at least 50,000. Build these from the inside out: first the OR, then wrap it in AND, then in IF.
5. IF between two values
=IF(AND(B2>=18, B2<=60), "Eligible", "Not eligible")Excel has no BETWEEN function. Test the lower and the upper limit with AND. For dates, use DATE: =IF(AND(A2>=DATE(2026,4,1), A2<=DATE(2027,3,31)), "FY 2026-27", "").
6. IF with NOT
=IF(NOT(E2="Cancelled"), F2, 0)Counts the amount for every order that is not cancelled. E2<>"Cancelled" does the same job and is shorter.
7. Nested IF (more than two outcomes)
=IF(B2>=75, "A", IF(B2>=60, "B", IF(B2>=40, "C", "Fail")))Excel checks the conditions from left to right and stops at the first true one, so start with the highest band. If you start with B2>=40, every pass mark becomes "C".
8. IFS: nested IF without the brackets
=IFS(B2>=75, "A", B2>=60, "B", B2>=40, "C", TRUE, "Fail")IFS (Excel 2019 and later) lists condition/result pairs. The final TRUE, "Fail" acts as the "else". Without it, a value that matches nothing returns #N/A.
9. Replace long nested IFs with a lookup table
When you have many bands, put them in a small table instead of a formula:
| Min mark (H) | Grade (I) |
|---|---|
| 0 | Fail |
| 40 | C |
| 60 | B |
| 75 | A |
=XLOOKUP(B2, $H$2:$H$5, $I$2:$I$5, , -1)The last argument -1 means "exact match or next smaller", so 68 finds 60 and returns B. To change a band, edit the table, not every formula. In older Excel, =VLOOKUP(B2, $H$2:$I$5, 2, TRUE) does the same with the table sorted ascending.
10. Practical business examples
Commission
=IF(F2>=100000, F2*10%, IF(F2>=50000, F2*5%, 0))Overdue invoices
=IF(AND(G2="Unpaid", TODAY()>H2), "Overdue", "")Leave blank if the input is empty
=IF(B2="", "", IF(B2>=40, "Pass", "Fail"))Without the first check, empty rows would show "Fail", because Excel treats an empty cell as 0.
Count with conditions (no IF needed)
=COUNTIFS(E2:E100, "North", F2:F100, ">=50000")
=SUMIFS(F2:F100, E2:E100, "North")For counting and summing with conditions, COUNTIFS and SUMIFS are simpler than an IF in a helper column.
Common mistakes
Text without quotes
=IF(B2>=40, Pass, Fail) returns #NAME?. Text must be in quotes: "Pass".
Writing AND like a sentence
=IF(B2>=40 AND C2>=40, …) does not work. Use the function: AND(B2>=40, C2>=40).
Numbers stored as text
If B2>=40 is FALSE for a value that looks like 50, the number is stored as text. Convert it (Data → Text to Columns → Finish) or use VALUE(B2).
Wrong order in nested IF
Always test the strictest condition first (highest mark, biggest sale), or later conditions are never reached.
Too many nested IFs
Excel allows 64 levels, but anything over three or four is hard to read and easy to break. Switch to IFS or a lookup table.
Frequently asked questions
How do I use IF with multiple conditions in Excel?
Put the conditions inside AND (all must be true) or OR (any can be true), then use that as the IF test: =IF(AND(A2>10, B2="Yes"), "OK", "Check").
What is the difference between IF and IFS?
IF handles one test with two results; you nest it for more. IFS takes many test/result pairs in one function and is easier to read.
Can IF return a formula result instead of text?
Yes. Any argument can be a calculation, for example =IF(F2>=50000, F2*5%, 0).
How do I check if a cell contains certain text?
Use SEARCH inside IF: =IF(ISNUMBER(SEARCH("pro", C2)), "Pro model", "").
Practice task
Create a sheet with Name, Region and Sales. Add three columns: a Pass/Fail target check (sales at least 50,000), a Bonus check (North or West and at least 50,000), and a Grade using a lookup table with four bands. Change some sales values and watch every result update.
Next: filter the rows that meet your conditions with the Excel FILTER function, and compare lookup formulas in XLOOKUP vs VLOOKUP vs INDEX MATCH.
.png)
.png)
