formulastreak

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.

March household budget · edit E2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultE2 = OK
ABCDE
1CategoryTypeBudgetActualStatus
2RentFixed$1,200.00$1,200.00OK
3GroceriesVariable$450.00$512.40Over
4UtilitiesFixed$160.00$148.75OK
5TransportVariable$120.00$96.50OK
6Phone & internetFixed$65.00$65.00OK
7Eating outVariable$150.00$203.10Over
8SubscriptionsFixed$38.00$44.97Over
9GymFixed$40.00$40.00OK
10GiftsVariable$60.00$25.00OK
11SavingsFixed$300.00$300.00OK

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.

March household budget · edit E2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultE2 = (empty)
ABCDE
1CategoryTypeBudgetActualAction
2RentFixed$1,200.00$1,200.00
3GroceriesVariable$450.00$512.40Cut back
4UtilitiesFixed$160.00$148.75
5TransportVariable$120.00$96.50
6Phone & internetFixed$65.00$65.00
7Eating outVariable$150.00$203.10Cut back
8SubscriptionsFixed$38.00$44.97
9GymFixed$40.00$40.00
10GiftsVariable$60.00$25.00
11SavingsFixed$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").

Leaf & Loam orders · edit I2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultI2 = (empty)
ABCDEFGHI
1OrderDateCustomerProductCategoryQtyPriceStatusFollow up
210012 Mar 2026Mara OkaforMonsteraPlants1$34.00Shipped
310022 Mar 2026Jonas LindTerracotta potPots3$12.50Shipped
410033 Mar 2026Priya RamanSnake plantPlants2$22.00Shipped
510044 Mar 2026Tomás RuizWatering canTools1$18.00PendingCheck
610055 Mar 2026Elif DemirFernPlants4$9.50Shipped
710065 Mar 2026Mara OkaforPotting mixSoil2$7.25ReturnedCheck
810076 Mar 2026Kenji MoriPruning shearsTools1$24.00Shipped
910087 Mar 2026Ines VargaCeramic potPots2$19.00PendingCheck
1010098 Mar 2026Jonas LindMonsteraPlants1$34.00Shipped
1110109 Mar 2026Sofia RossiFernPlants6$9.50Shipped
12101110 Mar 2026Priya RamanPotting mixSoil5$7.25Shipped
13101211 Mar 2026Leo BrandtSnake plantPlants1$22.00PendingCheck

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.

Q3 sales team · edit H2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultH2 = On target
ABCDEFGH
1RepRegionJulAugSepTargetQ3 totalRating
2Amara OseiNorth$12,400$13,100$11,800$36,000$37,300On target
3Ben FischerSouth$9,800$10,250$12,100$30,000$32,150On target
4Lina ChenEast$15,200$14,800$16,900$45,000$46,900On target
5Diego AlvarezWest$8,700$9,100$8,400$28,000$26,200Below
6Eva NovakNorth$11,200$12,600$13,900$36,000$37,700On target
7Farid HaddadSouth$13,300$12,900$14,100$39,000$40,300On target
8Grace KimEast$10,100$9,600$10,800$33,000$30,500Below
9Hugo MartinWest$14,500$15,800$15,100$42,000$45,400On 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
Select H2 and type = to start its formula (in the cell or the formula bar)
ABCDEFGH
1RepRegionJulAugSepTargetQ3 totalBonus?
2Amara OseiNorth$12,400$13,100$11,800$36,000$37,300
3Ben FischerSouth$9,800$10,250$12,100$30,000$32,150
4Lina ChenEast$15,200$14,800$16,900$45,000$46,900
5Diego AlvarezWest$8,700$9,100$8,400$28,000$26,200
6Eva NovakNorth$11,200$12,600$13,900$36,000$37,700
7Farid HaddadSouth$13,300$12,900$14,100$39,000$40,300
8Grace KimEast$10,100$9,600$10,800$33,000$30,500
9Hugo MartinWest$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; use AND(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.

Practise it now