The first thing most people do when they need to assign letter grades or categorize sales tiers is type =IF(A1>90,"A",IF(A1>80,"B",... until their formula hits 64 levels deep — then they get #VALUE! or worse, silent logic errors. That’s not a limitation of Excel. It’s a failure of planning.
Quick Answer
Use =IF(condition1,value_if_true1,IF(condition2,value_if_true2,value_if_false2)). Start from the topmost logical priority, test each condition left-to-right, and always close every opening parenthesis. The max depth is 64 — but you’ll hit readability limits at 5.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Classic Nested IF | Type IF inside IF manually; press Alt+M+V to toggle formula auditing mode | 3–5 conditions with simple thresholds | Hard to read past 4 layers; no built-in error handling |
| IFS Function (Excel 2019+) | =IFS(condition1,value1,condition2,value2,...,TRUE,default) | 6+ conditions; avoids parentheses overload | Not available in Excel 2016 or earlier |
| CHOOSE + MATCH | =CHOOSE(MATCH(value,{0,60,70,80,90},1),"F","D","C","B","A") | Fixed numeric bins (e.g., grading scales) | Only works with sorted ascending arrays; fails on text |
| XLOOKUP with Array | =XLOOKUP(A2,{0,60,70,80,90},{"F","D","C","B","A"},"N/A",-1) | Dynamic lookups, missing-value fallbacks | Requires Excel 365 or 2021; array constants must be exact |
| Custom LAMBDA (Advanced) | Define reusable logic like =GradeScale(A2) in Name Manager | Teams reusing same logic across 20+ sheets | No backward compatibility; steep learning curve |
Method 1 Deep Dive
Let’s assign performance ratings to sales reps in column B (B2:B11) based on quarterly revenue in column A. Thresholds: $0–$49,999 = "Needs Review", $50,000–$99,999 = "Satisfactory", $100,000–$149,999 = "Strong", $150,000+ = "Top Performer".
You’d write this in C2:
=IF(A2<50000,"Needs Review",IF(A2<100000,"Satisfactory",IF(A2<150000,"Strong","Top Performer")))
Note: We use ascending thresholds with <, not >=. That’s the counterintuitive tip. Testing A2<50000 first lets later conditions assume A2>=50000 — no redundant checks. If you reverse the order, you’ll double-count or miss ranges.
Now fill down to C11. Try editing one — say, change "Strong" to "Solid". You’ll see Excel highlights matching parentheses in color as you move your cursor inside the formula bar. That’s your visual anchor. Don’t guess — watch the colors.
Sample data (A2:C11):
| Revenue (A) | Rep Name (B) | Rating (C) |
|---|---|---|
| $32,450 | Sarah Chen | Needs Review |
| $87,200 | Diego Morales | Satisfactory |
| $134,600 | Priya Patel | Strong |
| $178,900 | James Wu | Top Performer |
| $44,100 | Anya Petrova | Needs Review |
| $102,300 | Kenji Tanaka | Strong |
| $65,750 | Fatima Diallo | Satisfactory |
| $191,000 | Liam O’Sullivan | Top Performer |
| $28,800 | Maya Rodriguez | Needs Review |
| $115,200 | Tariq Hassan | Strong |
Method 2 Deep Dive
Now try the same logic with IFS — cleaner and safer. In D2, enter:
=IFS(A2<50000,"Needs Review",A2<100000,"Satisfactory",A2<150000,"Strong",A2>=150000,"Top Performer")
No nesting. No closing-parenthesis counting. Just condition-result pairs. But here’s the catch: IFS returns #N/A if *none* match — so always end with TRUE,"Default". Fix it:
=IFS(A2<50000,"Needs Review",A2<100000,"Satisfactory",A2<150000,"Strong",TRUE,"Top Performer")
This is why TRUE acts as the “else” clause. Put it last. Always.
Try breaking it: delete the TRUE line. Then type =A2 in A12 and drag D2 down to D12. You’ll get #N/A for any value ≥150000 — because no condition matched. That’s not an Excel bug. It’s the function working as designed.
Keyboard shortcut: Press Alt+M+V while editing any formula to open Formula Auditing > Evaluate Formula. Step through each condition. Watch how Excel resolves A2<50000 → TRUE → returns "Needs Review" — no guessing required.
Cheat Sheet
| Task | Formula Syntax | Cell Reference Example | Shortcut |
|---|---|---|---|
| Basic nested IF (3 levels) | =IF(A1>=90,"A",IF(A1>=80,"B","C")) | A1:A10 contains scores | Alt+M+V |
| IFS with fallback | =IFS(B2<0,"Error",B2<100,"Low",TRUE,"High") | B2:B20 has inventory counts | F2 (edit cell), then Alt+M+V |
| CHOOSE+MATCH for grades | =CHOOSE(MATCH(C2,{0,60,70,80,90},1),"F","D","C","B","A") | C2:C15 holds exam % | Ctrl+Shift+Enter (legacy arrays only) |
| XLOOKUP tier lookup | =XLOOKUP(D2,{0,50000,100000,150000},{"Entry","Mid","Senior","Lead"},"N/A",-1) | D2:D12 has salary figures | Alt+M+L (open Name Manager for LAMBDA setup) |
