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 |
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","Blinkit","AMN","Amazon Now","Unknown")
Row 7 (Pune, 8 mins) → Pune 10-min hero; row 9 (Returned) → Lost order; row 4 → Amazon Now.
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 Blinkit 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).