Excel IF + AND + OR: Multi-Condition Logic
Use AND and OR inside IF to test multiple conditions cleanly. Handle complex business rules without deeply nested formulas.
Formula Genius can generate a draft from a plain-English prompt. Supported formulas can be checked with sample cases; review the coverage on the result.
The formula
"Flag orders as 'Priority' if amount is over $5000 AND status is 'Pending', or if the customer is VIP regardless of amount"
=IF(OR(AND(B2>5000, C2="Pending"), D2="VIP"), "Priority", "Standard")OR checks two paths: (1) AND requires both amount>5000 AND status=Pending, (2) customer=VIP alone. If either path is true, the order is Priority. Otherwise, Standard.
Step-by-step breakdown
- Outer IF(test, true_result, false_result) wraps the logic
- OR(condition1, condition2) — true if either condition passes
- AND(B2>5000, C2="Pending") — true only if BOTH are true
- D2="VIP" — second OR condition, independent of amount
- Result: Priority if big pending order OR VIP customer
Edge cases & warnings
- AND/OR return TRUE/FALSE, not values — always wrap in IF for custom output
- Empty cells in comparisons: empty cell > 5000 is FALSE, empty cell = "" is TRUE
- AND evaluates all arguments even if the first is FALSE (no short-circuit)
- For 4+ conditions, consider IFS or a lookup table approach
Examples
"Amount: $6000, Status: Pending, Customer: Regular"
Priority"Amount: $200, Status: Shipped, Customer: VIP"
Priority"Amount: $200, Status: Shipped, Customer: Regular"
StandardFrequently asked questions
How many conditions can AND/OR handle?
Up to 255 conditions each. But for readability, if you have more than 3-4, consider IFS, SWITCH, or a lookup approach.
Can I nest AND inside OR?
Yes, and vice versa. =IF(AND(OR(A1>5, A1<-5), B1="Active"), ...) checks if A1 is outside -5 to 5 AND B1 is Active.
Can't find what you need?
Describe a formula in plain English. Formula Genius drafts and explains it, and can check supported formulas on sample cases.