IF with AND and OR
Updated · Works in: all current Excel versions, Google Sheets · Every example checked before release
Short answer
Put AND or OR inside IF's condition: =IF(AND(B2="Variable",D2>C2),"Cut back","") needs both conditions true; =IF(OR(…),…) needs at least one. For three or more results, nest IFs (highest bar first) or use IFS.
IF picks one of two results. AND and OR let that choice depend on several conditions, and nesting gives you more than two results. Each example is a live sheet.
IF in one line
IF(condition, value_if_true, value_if_false). Conditions use =, <>, >, <, >= and <=; on its own, a condition like =D2>C2 shows TRUE or FALSE. Text results need double quotes.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Category | Type | Budget | Actual | Status |
| 2 | Rent | Fixed | $1,200.00 | $1,200.00 | OK |
| 3 | Groceries | Variable | $450.00 | $512.40 | Over |
| 4 | Utilities | Fixed | $160.00 | $148.75 | OK |
| 5 | Transport | Variable | $120.00 | $96.50 | OK |
| 6 | Phone & internet | Fixed | $65.00 | $65.00 | OK |
| 7 | Eating out | Variable | $150.00 | $203.10 | Over |
| 8 | Subscriptions | Fixed | $38.00 | $44.97 | Over |
| 9 | Gym | Fixed | $40.00 | $40.00 | OK |
| 10 | Gifts | Variable | $60.00 | $25.00 | OK |
| 11 | Savings | Fixed | $300.00 | $300.00 | OK |
AND: every condition must be true
AND(a, b, …) is TRUE only when all of its conditions are. Here a category gets flagged only if it's a Variable cost and over budget.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Category | Type | Budget | Actual | Action |
| 2 | Rent | Fixed | $1,200.00 | $1,200.00 | |
| 3 | Groceries | Variable | $450.00 | $512.40 | Cut back |
| 4 | Utilities | Fixed | $160.00 | $148.75 | |
| 5 | Transport | Variable | $120.00 | $96.50 | |
| 6 | Phone & internet | Fixed | $65.00 | $65.00 | |
| 7 | Eating out | Variable | $150.00 | $203.10 | Cut back |
| 8 | Subscriptions | Fixed | $38.00 | $44.97 | |
| 9 | Gym | Fixed | $40.00 | $40.00 | |
| 10 | Gifts | Variable | $60.00 | $25.00 | |
| 11 | Savings | Fixed | $300.00 | $300.00 |
OR: at least one is true
OR(a, b, …) is TRUE when any condition is. Note that each comparison is written out in full: OR(H2="Pending",H2="Returned"), not H2=OR("Pending","Returned").
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | Date | Customer | Product | Category | Qty | Price | Status | Follow up |
| 2 | 1001 | 2 Mar 2026 | Mara Okafor | Monstera | Plants | 1 | $34.00 | Shipped | |
| 3 | 1002 | 2 Mar 2026 | Jonas Lind | Terracotta pot | Pots | 3 | $12.50 | Shipped | |
| 4 | 1003 | 3 Mar 2026 | Priya Raman | Snake plant | Plants | 2 | $22.00 | Shipped | |
| 5 | 1004 | 4 Mar 2026 | Tomás Ruiz | Watering can | Tools | 1 | $18.00 | Pending | Check |
| 6 | 1005 | 5 Mar 2026 | Elif Demir | Fern | Plants | 4 | $9.50 | Shipped | |
| 7 | 1006 | 5 Mar 2026 | Mara Okafor | Potting mix | Soil | 2 | $7.25 | Returned | Check |
| 8 | 1007 | 6 Mar 2026 | Kenji Mori | Pruning shears | Tools | 1 | $24.00 | Shipped | |
| 9 | 1008 | 7 Mar 2026 | Ines Varga | Ceramic pot | Pots | 2 | $19.00 | Pending | Check |
| 10 | 1009 | 8 Mar 2026 | Jonas Lind | Monstera | Plants | 1 | $34.00 | Shipped | |
| 11 | 1010 | 9 Mar 2026 | Sofia Rossi | Fern | Plants | 6 | $9.50 | Shipped | |
| 12 | 1011 | 10 Mar 2026 | Priya Raman | Potting mix | Soil | 5 | $7.25 | Shipped | |
| 13 | 1012 | 11 Mar 2026 | Leo Brandt | Snake plant | Plants | 1 | $22.00 | Pending | Check |
More than two results
Put an IF where another IF says false, testing the highest bar first. Excel 2019+, Microsoft 365 and Google Sheets also offer IFS(cond1, value1, cond2, value2, …), which reads more easily.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Rep | Region | Jul | Aug | Sep | Target | Q3 total | Rating |
| 2 | Amara Osei | North | $12,400 | $13,100 | $11,800 | $36,000 | $37,300 | On target |
| 3 | Ben Fischer | South | $9,800 | $10,250 | $12,100 | $30,000 | $32,150 | On target |
| 4 | Lina Chen | East | $15,200 | $14,800 | $16,900 | $45,000 | $46,900 | On target |
| 5 | Diego Alvarez | West | $8,700 | $9,100 | $8,400 | $28,000 | $26,200 | Below |
| 6 | Eva Novak | North | $11,200 | $12,600 | $13,900 | $36,000 | $37,700 | On target |
| 7 | Farid Haddad | South | $13,300 | $12,900 | $14,100 | $39,000 | $40,300 | On target |
| 8 | Grace Kim | East | $10,100 | $9,600 | $10,800 | $33,000 | $30,500 | Below |
| 9 | Hugo Martin | West | $14,500 | $15,800 | $15,100 | $42,000 | $45,400 | On target |
Check yourself
One real exercise from the “Decisions” unit. Checked on our server; no account needed.A rep earns a "Bonus" when their Q3 total (G2) is at least their Target (F2) and September (E2) beat July (C2). Otherwise show nothing (""). Start in H2.
- Use AND
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Rep | Region | Jul | Aug | Sep | Target | Q3 total | Bonus? |
| 2 | Amara Osei | North | $12,400 | $13,100 | $11,800 | $36,000 | $37,300 | |
| 3 | Ben Fischer | South | $9,800 | $10,250 | $12,100 | $30,000 | $32,150 | |
| 4 | Lina Chen | East | $15,200 | $14,800 | $16,900 | $45,000 | $46,900 | |
| 5 | Diego Alvarez | West | $8,700 | $9,100 | $8,400 | $28,000 | $26,200 | |
| 6 | Eva Novak | North | $11,200 | $12,600 | $13,900 | $36,000 | $37,700 | |
| 7 | Farid Haddad | South | $13,300 | $12,900 | $14,100 | $39,000 | $40,300 | |
| 8 | Grace Kim | East | $10,100 | $9,600 | $10,800 | $33,000 | $30,500 | |
| 9 | Hugo Martin | West | $14,500 | $15,800 | $15,100 | $42,000 | $45,400 |
Common mistakes
Forgetting quotes around text results:
IF(A1>5,Yes,No)returns#NAME?.Testing the lowest bar first in a nested IF, so the higher outcomes are never reached.
Writing
A1>5 AND A1<10. Spreadsheets have no AND operator; useAND(A1>5,A1<10).
Questions
How many IFs should I nest?+
Beyond three or four levels, a small lookup table with XLOOKUP (match mode -1) or VLOOKUP (approximate match) is far easier to read and change.
What does IF return if I leave out the false value?+
FALSE. Use `""` as the third argument to show an empty-looking cell instead.
Reading is step one. Now make it stick.
Practise it in a five-minute lesson on a live sheet, with instant feedback. No account needed for the free units.