Nested IF Formula in Excel and Google Sheets
Purpose
Nested IF is where people either fall in love with Excel or get completely lost in it. A nested IF…
Nested IF is where people either fall in love with Excel or get completely lost in it. A nested IF is simply one IF inside another — when the first IF does not fire, it falls through to a second IF that tries a different condition, and so on. This lets you handle three or more outcomes from a single formula. A grade assignment is the classic example: A for 90 and above, B for 80 to 89, C for 70 to 79, and F for everything below. That is four outcomes, which means three nested IFs. The key to getting nested IF right is order: you must check the most restrictive condition first. If you check the weakest condition first, every value that meets it will stop there and never reach the more specific conditions. I have seen this exact mistake in dozens of spreadsheets, always producing wrong results that take hours to debug. Get the order right and nested IF is reliable and powerful.
Syntax
✓ Excel 2003+
✓ Google Sheets
= Same syntax
=IF(test1, result1, IF(test2, result2, IF(test3, result3, else_result)))
Arguments
| Argument | Required | Description |
|---|---|---|
| test1 | Required | The first condition to evaluate. If true, result1 is returned and the rest of the formula is ignored. If false, the formula moves to the nested IF in the value_if_false position. |
| result1 | Required | The value to return when test1 is true. Can be text, a number, a blank, or a formula. |
| test2 | Required | The second condition, tested only when test1 is false. Works the same as test1. |
| else_result | Required | The final value to return when all conditions are false. This is the catch-all that handles any rows that did not match any of the tested conditions. |
How it works
A nested IF works by placing another IF into the value_if_false position of the outer IF. When the first condition is false, Excel does not return a simple value — it evaluates the second IF. If the second condition is also false, it evaluates the third IF, and so on. The chain continues until either a condition is true and that result is returned, or all conditions are false and the final else result is returned. This evaluation order is what makes condition ordering critical. Excel stops at the first true condition it encounters going from left to right through the nesting. A value of 85 tested against >=90, >=80, >=70 in that order will correctly reach >=80 and return B. But if you wrote >=70 first, 85 would match that immediately and return C — the wrong grade. Always order conditions from most specific to least specific.
Examples
1
Assign a grade letter to each student based on their score using nested IF.
| A | B | C | |
|---|---|---|---|
| 1 | Student | Score | Grade |
| 2 | Alice | 94 | =IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C","F"))) |
| 3 | Bob | 82 | B |
| 4 | Carol | 71 | C |
| 5 | David | 68 | F |
| 6 | Eve | 90 | A |
| 7 | Frank | 55 | F |
| 8 | Grace | 88 | B |
| 9 | Hank | 79 | C |
| 10 | Iris | 96 | A |
| 11 | James | 73 | C |
Row 2: A
— Alice scored 94. The first test B2>=90 is true so A is returned immediately. The remaining IFs are not evaluated.
Eve scored exactly 90 and also gets A because >= includes the boundary. David scored 68 — he fails the >=90, >=80, and >=70 tests so he falls through to the final else and gets F.
2
Calculate a tiered delivery charge based on order weight — Free under 1kg, Standard up to 5kg, Express up to 20kg, Freight above that.
| A | B | C | |
|---|---|---|---|
| 1 | Order | Weight (kg) | Delivery Tier |
| 2 | ORD-001 | 0.5 | =IF(B2<1,"Free",IF(B2<=5,"Standard",IF(B2<=20,"Express","Freight"))) |
| 3 | ORD-002 | 3.2 | Standard |
| 4 | ORD-003 | 18.5 | Express |
| 5 | ORD-004 | 0.8 | Free |
| 6 | ORD-005 | 45.0 | Freight |
| 7 | ORD-006 | 1.0 | Standard |
| 8 | ORD-007 | 6.1 | Express |
| 9 | ORD-008 | 0.3 | Free |
| 10 | ORD-009 | 21.0 | Freight |
| 11 | ORD-010 | 4.9 | Standard |
Row 2: Free
— ORD-001 weighs 0.5kg which is below 1kg, so the first condition is true and Free is returned.
Notice the condition order: the smallest threshold (under 1) is tested first. ORD-006 weighs exactly 1.0kg — it fails the <1 test so it moves to <=5 and gets Standard correctly.
3
Assign a risk label based on a credit score — using three nested IFs for four tiers.
| A | B | C | |
|---|---|---|---|
| 1 | Applicant | Credit Score | Risk Level |
| 2 | Alice Chen | 780 | =IF(B2>=750,"Low",IF(B2>=650,"Moderate",IF(B2>=550,"High","Very High"))) |
| 3 | Bob Smith | 690 | Moderate |
| 4 | Carol Jones | 520 | Very High |
| 5 | David Kim | 750 | Low |
| 6 | Eve Taylor | 610 | High |
| 7 | Frank Brown | 820 | Low |
| 8 | Grace Lee | 545 | Very High |
| 9 | Hank Patel | 670 | Moderate |
| 10 | Iris Wong | 580 | High |
| 11 | James Miller | 640 | Moderate |
Row 2: Low
— Alice has a score of 780 which passes the first test >=750, returning Low immediately.
David scored exactly 750 and also gets Low because >= includes the boundary. Carol at 520 fails all three tests and reaches the final else — Very High.
Common use cases
1. Assign shipping tier based on order weight: Standard, Express, or Freight
=IF(B2<=1,"Standard",IF(B2<=10,"Express","Freight"))
2. Label risk scores as Low, Medium, or High based on a numeric score
=IF(C2<30,"Low",IF(C2<70,"Medium","High"))
3. Assign commission tier based on quarterly sales performance
=IF(D2>=50000,D2*0.15,IF(D2>=30000,D2*0.10,D2*0.05))
4. Return a department description from a department code
=IF(A2="SLS","Sales",IF(A2="MKT","Marketing",IF(A2="ENG","Engineering","Other")))
5. Categorise transactions as Small, Medium, Large, or Enterprise based on value
=IF(B2>=100000,"Enterprise",IF(B2>=10000,"Large",IF(B2>=1000,"Medium","Small")))
Common errors
Returns wrong category for some values
The most common nested IF mistake — conditions tested in the wrong order. If >=70 is tested before >=80, a score of 85 matches >=70 first and returns the wrong result.
Fix: Always order conditions from most restrictive to least restrictive. For greater-than comparisons, start with the highest threshold. For less-than comparisons, start with the lowest threshold.
Formula is too hard to read and maintain
With four or more nesting levels, nested IF becomes difficult to debug and easy to break when requirements change.
Fix: Switch to IFS for flat structure: =IFS(A1>=90,"A",A1>=80,"B",A1>=70,"C",TRUE,"F"). Or use a lookup table with VLOOKUP in approximate match mode — far easier to maintain.
Missing closing parentheses
Each nested IF adds another opening parenthesis that needs a matching closing one. With three nested IFs you need four closing parentheses at the end.
Fix: Count your opening parentheses. For every IF() there must be exactly three arguments and one closing parenthesis. Excel highlights mismatched parentheses in red to help you find the issue.
Tips and variations
Use IFS instead of nested IF for three or more conditions
IFS reads as a flat list of condition-result pairs which is much easier to follow than nested IFs. =IFS(A1>=90,"A",A1>=80,"B",A1>=70,"C",TRUE,"F") does exactly the same job as three nested IFs. The TRUE at the end acts as the else condition. IFS is available in Excel 2019+ and all versions of Google Sheets.
=IFS(A1>=90,"A",A1>=80,"B",A1>=70,"C",TRUE,"F")
Use a lookup table for categories that change often
If the thresholds or categories change regularly, maintain a small lookup table and use VLOOKUP with approximate match instead of a nested IF. Updating the table takes seconds versus debugging a long nested formula. The table approach also handles more tiers without the formula growing longer.
=VLOOKUP(A2,GradeTable,2,TRUE)
Add line breaks in the formula bar to make nested IF readable
In Excel you can press Alt+Enter inside the formula bar to add a line break without entering the formula. Breaking a nested IF across multiple lines makes the nesting levels visible at a glance. Google Sheets does not support this but you can use spaces to indent.
=IF(B2>=90,"A", IF(B2>=80,"B", IF(B2>=70,"C","F")))
Excel vs Google Sheets
Excel vs Google Sheets
Nested IF works identically in Excel and Google Sheets. Same syntax, same evaluation order, same maximum nesting depth. IFS is also available in both applications. Formulas copy between applications without changes.
Related reading
Frequently asked questions
Excel supports up to 64 nested IF functions in a single formula. In practice, more than 3 or 4 levels becomes extremely hard to read and maintain. If you need more than 4 conditions, use IFS (Excel 2019+) or a VLOOKUP/XLOOKUP against a lookup table instead — both are far more readable and maintainable.
Nested IF checks conditions one at a time inside each other, which creates deeply indented formulas that are hard to read. IFS checks multiple conditions in sequence in a flat structure: =IFS(A1>90,"A",A1>80,"B",A1>70,"C",TRUE,"F"). IFS is cleaner and easier to maintain. The TRUE at the end acts as the else clause.
The most common issue is condition order. Nested IF stops at the first true condition it finds. If you put >=70 before >=80, a value of 85 will match >=70 first and return the wrong grade. Always order conditions from most restrictive (highest threshold) to least restrictive (lowest threshold) to ensure each branch captures only what it should.
Yes — text conditions work the same way. =IF(A1="Manager","Senior",IF(A1="Senior","Mid","Junior")) assigns levels based on job title. Note that IF text comparisons are case-insensitive — Manager and manager both match.
Yes — a lookup table with VLOOKUP or XLOOKUP in approximate match mode is cleaner for range-based categorisation. Create a table with threshold values and categories, then use =VLOOKUP(A1,table,2,TRUE). This is far easier to maintain when thresholds change, because you update the table rather than editing a complex nested formula.