Concept
1. All three at a glance
Each of these returns TRUE or FALSE on its own.
| Function | TRUE when | Example |
|---|---|---|
AND |
all conditions are true | =AND(B2>=40, C2>=40) |
OR |
any one condition is true | =OR(D2="Delhi", D2="Mumbai") |
NOT |
reverses a condition | =NOT(D2="Delhi") |
AND/OR accept up to 255 conditions.
2. Practice sheet
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Candidate | Written | Interview | City |
| 2 | Rahul | 72 | 65 | Delhi |
| 3 | Pooja | 85 | 38 | Jaipur |
| 4 | Vikas | 45 | 80 | Mumbai |
| 5 | Anjali | 35 | 30 | Delhi |
3. AND + IF: both must pass
Selected only if both written and interview are 40+:
=IF(AND(B2>=40, C2>=40), "Selected", "Rejected")
Result: Rahul Selected, Pooja Rejected, Vikas Selected, Anjali Rejected.
4. OR + IF: any one is enough
Shortlist if either round is 80+:
=IF(OR(B2>=80, C2>=80), "Shortlist", "-")
Pooja (85) and Vikas (80) are shortlisted.
5. NOT: reverse a condition
Travel allowance for candidates outside Delhi:
=IF(NOT(D2="Delhi"), "TA Eligible", "Local")
=IF(D2<>"Delhi", ...) does the same job. NOT is really useful when you need to reverse an entire AND/OR condition, like NOT(OR(...)) — "none of these".
6. AND + OR together
Selected if both rounds are 40+ or written is 90+ (direct entry):
=IF(OR(AND(B2>=40, C2>=40), B2>=90), "Selected", "Rejected")
Read from the inside out: first the AND result, then combine it with the other condition in OR.
7. Shortening nested IFs
Without AND:
=IF(B2>=40, IF(C2>=40, "Selected", "Rejected"), "Rejected")
With AND:
=IF(AND(B2>=40, C2>=40), "Selected", "Rejected")
Same result, half the formula, "Rejected" written only once.
Common mistakes
Writing it maths-style: =IF(40<=B2<=100, ...) doesn't work in Excel — write AND(B2>=40, B2<=100). OR(D2="Delhi","Mumbai") is also wrong; each condition must be written in full: OR(D2="Delhi", D2="Mumbai"). And using AND/OR on their own outside IF while expecting text like "Selected" — they only return TRUE/FALSE.