3.14 AND, OR, NOT and SWITCH
| Function | Example | Meaning |
|---|---|---|
AND(l1, l2, …) |
=AND(C2="Pune",H2<=10) |
TRUE only if all are TRUE |
OR(l1, l2, …) |
=OR(I2="Cancelled",I2="Returned") |
TRUE if any is TRUE |
NOT(logical) |
=NOT(I2="Delivered") |
Reverses TRUE/FALSE |
SWITCH(expr, v1, r1, …, [default]) (Excel 2019+) |
see below | Match one value against a list |
About this data: Teaching sample for a fictional shop “BrightLedger Finance”. Figures and names (e.g. Neha Shah) are made up for this lesson on and or not and switch.
Worked example – combine with IF.
=IF(AND(C2="Pune",H2<=10),"Pune 10-min hero","")
=IF(OR(I2="Cancelled",I2="Returned"),"Lost order","OK")
=SWITCH(LEFT(A2,3),"BLK","FreshGo","AMN","MarketRush","Unknown")
Row 7 (Pune, 8 mins) → Pune 10-min hero; row 9 (Returned) → Lost order; row 4 → MarketRush.
Ravindra Bagale's Tip
Many students write it like English: =IF(C2="Pune" AND H2<=10, …) – but in Excel, AND is a function, not an operator. Write it as AND(condition1, condition2). SWITCH only checks "equal to" – for comparisons like >, use IFS.
Ravindra Bagale's Tip – मराठी
बरेच students =IF(C2="Pune" AND H2<=10, …) असं English सारखं लिहितात – Excel मध्ये AND हे function आहे, operator नाही. AND(condition1, condition2) असंच लिहा. SWITCH फक्त "equal to" check करतो – > सारख्या comparisons साठी IFS वापरा.
Ravindra Bagale's Tip – हिंदी
बहुत से students =IF(C2="Pune" AND H2<=10, …) ऐसे English की तरह लिखते हैं – Excel में AND एक function है, operator नहीं. AND(condition1, condition2) ऐसे ही लिखो. SWITCH सिर्फ़ "equal to" check करता है – > जैसे comparisons के लिए IFS इस्तेमाल करो.
Practice task
Flag orders that are FreshGo AND Fruits AND more than ₹200. Use SWITCH to convert city names to codes (Pune → PUN, Nashik → NSK, Nagpur → NGP, Kolhapur → KOP, Solapur → SLP, Sambhaji Nagar → SBN).
Loading practice tasks...
- Fast Pune order (Easy): Check if C7 is "Pune" AND H7 is 10 or less.
- Lost order (Easy): Check if I9 is "Cancelled" OR "Returned".
- Claim: fast track (Easy): Check if Claims!D2 is 7 or less AND Claims!E2 is "Yes".
- Claim: needs a check (Easy): Check if Claims!C3 is more than 100000 OR Claims!D3 is more than 7.
- Not delivered (Medium): Check that I5 is NOT "Delivered".
- Hero tag (Medium): If C7 is "Pune" and H7 is 10 or less show "Pune 10-min hero", else "".
- Claim: papers missing (Medium): Check that Claims!E3 is NOT "Yes".
- Platform name from code (Hard): Look at the first 3 letters of A4. "BLK" means "FreshGo", "AMN" means "MarketRush". Anything else is "Unknown".
- City code (Hard): Use SWITCH on C3: "Pune" = "PUN", "Nashik" = "NSK", else "Other".
- Claim: type name (Hard): Use SWITCH on Claims!B5: "HLT" = "Health", "MTR" = "Motor", "TRV" = "Travel", else "Other".
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
