IF with AND/OR - Translate multi-rule policies
Separate the combined logical test from the action it selects, and choose the connector that matches the policy.
By the end, you can
- Translate every versus any requirement.
- Place an AND/OR result inside IF's logical test.
- Check mixed policies and boundary cases.
Before you start: Read IF, AND, and OR first. Each plays a different role in the combined formula.
In this lesson
Build the test, then choose the action
AND and OR answer a logical question; IF turns the answer into an action or value. Keep those roles separate: write the individual comparisons, decide how they connect, then choose the TRUE and FALSE outcomes. This avoids hiding a policy mistake inside a long formula.
- Use IF(AND(...), outcome, alternative) when every condition is required.
- Use IF(OR(...), outcome, alternative) when any condition is sufficient.
- Inner parentheses close the logical function before the comma separating IF's outcomes.
Bonus eligibility requires both targets
Eligible| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Salesperson | Revenue INR | Target INR | Satisfaction | Bonus |
| 2 | Anika | ₹12,500 | ₹10,000 | 94 | Eligible |
| 3 | Dev | ₹9,800 | ₹10,000 | 95 | Not eligible |
| 4 | Meera | ₹15,000 | ₹12,000 | 87 | Not eligible |
Either reason can trigger priority follow-up
Priority| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer | Contract INR | Complaints | Follow-up |
| 2 | Apex | ₹62,000 | 0 | Priority |
| 3 | Bright | ₹24,000 | 2 | Priority |
| 4 | Core | ₹18,000 | 0 | Routine |
=IF(OR(B2>=50000,C2>0),"Priority","Routine")Apex qualifies through value. OR need not have both conditions TRUE.
Group alternatives inside a requirement
Suppose a separate review process allows standard review only for contracts of at least ₹20,000 with either zero or one complaint. The value threshold is mandatory; the two complaint counts are alternatives. Nest OR inside AND to preserve that grouping.
=IF(AND(B2>=20000,OR(C2=0,C2=1)),"Standard review","Escalate")Apex meets the value threshold and has zero complaints. The grouped OR is TRUE, so AND is TRUE.
This is a separate policy from the priority list above. Parentheses express grouping; merely rearranging the words AND and OR can change which accounts qualify.
Limits and common mistakes
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.