📎 This article includes 1 downloadable practice file ↓
In this article
Nested IFs like =IF(K2>=10000,"A",IF(K2>=3000,"B","C")) work, but by the fourth level nobody, including you next month, can read them. Excel has cleaner tools.
IFS: slabs and grades
=IFS(K2>=10000, "A", K2>=3000, "B", TRUE, "C")Conditions are checked in order and the first TRUE wins, so start with the strictest. The final TRUE, "C" is the “everything else” catch; without it, unmatched rows give #N/A.
SWITCH: one value, many outcomes
=SWITCH(D2, "North","N", "West","W", "South","S", "East","E", "?")Use SWITCH when you compare one cell against exact values. Use IFS for ranges (above, below).
AND, OR, NOT
| Question | Formula |
|---|---|
| Big Electronics sale? | =AND(H2="Electronics",K2>5000) |
| Needs follow-up? | =OR(L2="Pending",L2="Overdue") |
| Not paid? | =NOT(L2="Paid") or simply =L2<>"Paid" |
| Commission 5% above ₹10,000, else 2% | =K2*IF(K2>10000,5%,2%) |
AND/OR when counting
- AND: COUNTIFS with several criteria.
=COUNTIFS(H:H,"Furniture",L:L,"Paid") - OR: add COUNTIFs.
=COUNTIF(D:D,"North")+COUNTIF(D:D,"East") - Not equal:
"<>Paid"
XLOOKUP(value, slab_start, rate,, -1) instead of writing the numbers into IFS.Common mistakes
- Wrong order in IFS: testing
>=3000before>=10000means nobody ever gets an A. - AND inside COUNTIF:
COUNTIF(range, AND(...))doesn’t work; use COUNTIFS.
Practice
Download this lesson’s workbook below. Row-level tasks point to a specific row of the Sales sheet. Type your formula in the yellow column; the Check column turns green when the result matches, and the Answers sheet has a working formula for every task.
📎 Practice files for this article
- 📗Lesson 7 practice workbookSales register + 8 logic tasks.⬇ XLSX · 19 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.