IF With AND and OR in Excel: Formulas That Work
· 7 min read
To test more than one condition with an Excel IF AND OR formula, put AND or OR inside the first argument of IF. AND returns TRUE only when every condition is true. OR returns TRUE when at least one is. IF then picks the result:
=IF(AND(condition1, condition2), value_if_true, value_if_false)
=IF(OR(condition1, condition2), value_if_true, value_if_false)That is the whole pattern behind every "if and or" question. The rest of this guide shows it on real-looking data, plus nested IF for more than two outcomes.
The example table
Five orders from a business supplies company. Columns E to I hold the formulas from the sections below, one per column, filled down to row 6:
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | Segment | Amount | Status | Size | Priority | Review | Check | Bonus |
| 2 | 3001 | Enterprise | 1,800 | completed | Large | Priority | OK | Yes | |
| 3 | 3002 | SMB | 2,400 | completed | Large | Standard | Review | Yes | |
| 4 | 3003 | Enterprise | 650 | refunded | Small | Standard | Review | Check | No |
| 5 | 3004 | SMB | 300 | cancelled | Small | Standard | OK | Check | No |
| 6 | 3005 | Enterprise | 1,200 | completed | Large | Standard | OK | Yes |
Excel IF then: the basic formula
IF asks one question and returns one of two answers. "If the amount is at least 1,000, then Large, else Small":
=IF(C2>=1000, "Large", "Small")Filled down to row 6: Large, Large, Small, Small, Large. Text results go in quotes. Numbers and cell references do not.
The comparison operators are =, <> (not equal), >, <, >= and <=. Text comparisons are not case sensitive, so D2="Completed" is TRUE for "completed".
Excel IF AND: all conditions must be true
Flag priority orders: Enterprise customers and an amount of at least 1,500.
=IF(AND(B2="Enterprise", C2>=1500), "Priority", "Standard")Results: Priority, Standard, Standard, Standard, Standard. Only order 3001 meets both rules. Order 3002 is big enough but SMB. Orders 3003 and 3005 are Enterprise but too small.
AND can take up to 255 conditions. Each one is separated by a comma, and each one must be a full comparison.
Excel IF OR: one condition is enough
Send an order for review if it is over 2,000 or it was refunded:
=IF(OR(C2>2000, D2="refunded"), "Review", "OK")Results: OK, Review, Review, OK, OK. Order 3002 is over 2,000. Order 3003 was refunded. Neither of the others meets either rule.
OR on the same cell
A common use is checking one cell against several values: "is the status refunded or cancelled?"
=IF(OR(D2="refunded", D2="cancelled"), "Check", "")You must repeat D2= for each value. =OR(D2="refunded","cancelled") does not work the way it reads. Excel returns #VALUE! because "cancelled" on its own is not a TRUE or FALSE test.
IF and OR together with AND
You can nest AND and OR inside each other. A bonus applies to completed orders that are either Enterprise or at least 2,000:
=IF(AND(D2="completed", OR(B2="Enterprise", C2>=2000)), "Yes", "No")Results: Yes, Yes, No, No, Yes. Read it from the inside out. The OR gives TRUE for 3001, 3002, 3003 and 3005. The AND then removes 3003, because it was refunded.
When rules get this long, say them out loud first and write the brackets to match: "completed, AND (Enterprise OR big)". The brackets are the logic.
Nested IF: more than two outcomes
An IF inside an IF lets you return three or more results. Order tiers: Gold from 2,000, Silver from 1,000, otherwise Bronze.
=IF(C2>=2000, "Gold", IF(C2>=1000, "Silver", "Bronze"))Results: Silver, Gold, Bronze, Bronze, Silver.
Excel checks the conditions from left to right and stops at the first TRUE. That is why the order matters: always test the strictest condition first. If you swap them:
=IF(C2>=1000, "Silver", IF(C2>=2000, "Gold", "Bronze"))Order 3002 (2,400) now shows Silver. It passes the first test, so the Gold test never runs. No order can ever be Gold.
IFS: a flatter alternative
In Excel 2019 and later, Microsoft 365 and Excel for the web, IFS lists condition and result pairs without nesting:
=IFS(C2>=2000, "Gold", C2>=1000, "Silver", TRUE, "Bronze")The final TRUE, "Bronze" is the "everything else" case. Without it, a value that matches nothing returns #N/A. IFS still stops at the first TRUE, so the order rule applies here too. In the one-off purchase of Excel 2016 and earlier versions, IFS shows #NAME?, so stick with nested IF if the file has to work everywhere.
All three tier formulas on the five orders, plus the bonus amount from the next section:
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Order | Segment | Amount | Status | Tier | Tier, wrong order | Tier, IFS | 5% bonus |
| 2 | 3001 | Enterprise | 1,800 | completed | Silver | Silver | Silver | 90 |
| 3 | 3002 | SMB | 2,400 | completed | Gold | Silver | Gold | 0 |
| 4 | 3003 | Enterprise | 650 | refunded | Bronze | Bronze | Bronze | 0 |
| 5 | 3004 | SMB | 300 | cancelled | Bronze | Bronze | Bronze | 0 |
| 6 | 3005 | Enterprise | 1,200 | completed | Silver | Silver | Silver | 0 |
For a long list of exact values (such as region codes to names), a lookup table with XLOOKUP is easier to maintain than a long nested IF. See VLOOKUP vs XLOOKUP vs INDEX MATCH.
Returning numbers instead of text
IF can return calculations too. A 5% bonus on priority orders, zero for the rest:
=IF(AND(B2="Enterprise", C2>=1500), C2*5%, 0)For order 3001 that returns 90. The others return 0. You can see it in column H of the tier sheet above.
Common mistakes
Swapping AND and OR. This is the logic error AI assistants make most often, and it is easy to miss because the formula has no error. Ask for "Enterprise orders of 1,500 or more" and you may get OR(B2="Enterprise", C2>=1500). On our table that flags four orders (3001, 3002, 3003 and 3005) instead of one. To catch it, find a row that meets only one of the two rules and check its result. If an order that is SMB but large shows "Priority", the formula uses OR where it needs AND. The free Excel formula checker shows what each AND and OR test returns, one step at a time.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Segment | Amount | Status | AI's formula | Fixed |
| 2 | 3001 | Enterprise | 1,800 | completed | Priority | Priority |
| 3 | 3002 | SMB | 2,400 | completed | Priority | Standard |
| 4 | 3003 | Enterprise | 650 | refunded | Priority | Standard |
| 5 | 3004 | SMB | 300 | cancelled | Standard | Standard |
| 6 | 3005 | Enterprise | 1,200 | completed | Priority | Standard |
Writing AND like a sentence. =IF(B2="Enterprise" AND C2>=1500, ...) is how you would write it in SQL, but Excel will not accept it. AND and OR are functions in Excel: the word comes first and the conditions go in brackets.
Putting numbers in quotes. =IF(C2>="1000", ...) compares a number with the text "1000". Excel treats text as larger than any number in comparisons, so the condition is FALSE for every amount. Write C2>=1000 without quotes.
Chaining comparisons. =IF(1000<=C2<2000, ...) does not test a range. Excel works it out as (1000<=C2)<2000, which compares TRUE or FALSE with 2000 and gives a nonsense answer. Use AND(C2>=1000, C2<2000).
Wrong nesting order. Testing the loose condition first means the strict one is never reached, as in the Silver and Gold example above. Check the highest tier with a value that should land in it.
Missing the last case. Every nested IF needs a final "else". If you leave it out, rows that match nothing return FALSE, which looks like data.
FAQ
How do I use IF with AND and OR in one formula?
Nest them inside the first argument of IF, for example =IF(AND(D2="completed", OR(B2="Enterprise", C2>=2000)), "Yes", "No"). Brackets decide which conditions belong together.
Can I use IF with multiple conditions?
Yes. Use AND when all conditions must be true, OR when one is enough, and nested IF or IFS when you need more than two possible results.
How many IFs can you nest in Excel?
Up to 64 in current versions. If you need more than three or four, a lookup table is usually clearer.
Why does my IF formula always return FALSE?
Often the formula has no value_if_false and the condition is not met, or a number in the condition is in quotes. Check each part with a separate cell, for example =C2>=1000, to see which piece returns FALSE.
How do I count rows that meet several conditions instead of labelling them?
Use COUNTIFS. See COUNTIF with multiple criteria.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “Hit or missed?” →