🎯
What you'll learn: How to use IF for simple conditions, and combine it with AND / OR for multi-criteria decisions. These three functions together handle the majority of real-world business logic in Excel.
The IF Function
The IF function checks a condition that is either true or false. Based on the result, it returns one value if true, and a different value if false.
=IF(condition, value_if_true, value_if_false)
Worked Example — Salary Band Analysis
| Name | Basic Salary | Less than 5,000 | 5,000 or above |
|---|---|---|---|
| Vipin | 4,890 | ← IF fills this | 0 |
| Kaushal | 5,000 | 0 | ← IF fills this |
| Krishna | 5,234 | 0 | ← IF fills this |
| Udit | 6,000 | 0 | ← IF fills this |
| Rahul | 4,210 | ← IF fills this | 0 |
For the "Less than 5,000" column (where B2 is the Basic salary):
=IF(B2<5000, B2, 0)
— If salary is below 5000, show the salary; otherwise show 0
— If salary is below 5000, show the salary; otherwise show 0
For the "5,000 or above" column:
=IF(B2>=5000, B2, 0)
— If salary is 5000 or more, show it; otherwise show 0
— If salary is 5000 or more, show it; otherwise show 0
📥
Download the working example file to practice: ifandor.xls | ifandor.zip
Using OR and AND with IF
In real decisions, we often have multiple criteria. That is where OR and AND come in — they let you test several conditions at once inside your IF formula.
Example Dataset
| Name | Basic | HRA? | Telephone? | Poor | Rich |
|---|---|---|---|---|---|
| Vipin | 4,890 | N | Y | ||
| Kaushal | 5,000 | Y | N | ||
| Krishna | 5,234 | Y | Y | ||
| Udit | 6,000 | Y | Y | ||
| Rahul | 4,210 | N | N |
Using OR — Fill the "Rich" Column
Criteria: If Basic > 5000 OR HRA is given OR has Telephone → "YES", else "NO".
=IF(OR(B2>5000, C2="Y", D2="Y"), "YES", "NO")
Using AND — Fill the "Poor" Column
Criteria: If Basic < 5000 AND no HRA AND no Telephone → "YES", else "NO".
=IF(AND(B2<5000, C2="N", D2="N"), "YES", "NO")
OR vs AND — Key Difference
| Function | Returns TRUE when… | Best used for… |
|---|---|---|
OR | Any one condition is met | Anyone who qualifies on any benefit |
AND | All conditions must be met | People who meet every strict criterion |
💡
Pro tip — Nested IF: You can put an IF inside another IF for more levels:
=IF(A1>100,"High",IF(A1>50,"Medium","Low")). Excel supports up to 64 levels of nesting.