Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
Fix the Nested IF error
Excel & Google SheetsWhat Nested IF means
A nested IF chains multiple IF functions so that the value_if_false of one IF is another IF. When the result is wrong, the most common causes are conditions evaluated in the wrong order, overlapping condition boundaries returning the wrong branch, or unbalanced parentheses causing a parse error.
Common causes
- Conditions are ordered from smallest to largest (or largest to smallest) incorrectly — the first matching condition wins, so if >=50 comes before >=80, a score of 90 is caught by the >=50 branch and never reaches >=80.
- An unbalanced number of opening and closing parentheses causes a #VALUE! or parse error, especially in formulas with four or more levels.
- The final else branch is missing — the last IF has only two arguments, so when no condition matches, the formula returns FALSE instead of the intended default.
- Conditions use < and > but the boundary value is double-counted or missed between two branches — for example, >=50 and <50 leave a gap at exactly 50 only if > is used.
- Nested IFs exceed the nesting limit: 64 levels in modern Excel, 7 in Excel 2003 and older; hitting the limit produces a #VALUE! error.
Example fix
Broken
=IF(A2>=50,"Pass",IF(A2>=80,"Merit","Fail"))Fixed
=IF(A2>=80,"Merit",IF(A2>=50,"Pass","Fail"))The broken formula checks >=50 first, so any score of 80 or more is already captured as 'Pass' and never reaches the 'Merit' branch. Reversing the order — most restrictive condition first — fixes the logic.
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
How to fix it
- 1Order conditions from most restrictive to least restrictive: check >=90 first, then >=80, then >=70, else return the catch-all. This ensures the right branch is hit.
- 2Count parentheses: every IF needs one opening parenthesis and two commas inside, and one closing parenthesis. Use the formula bar syntax highlighting in Excel (each pair is a different colour) to spot mismatches.
- 3Always provide a final else value: =IF(A2>=90,"A",IF(A2>=80,"B","C")) — the last "C" is the else. Without it the formula returns FALSE when no condition matches.
- 4Replace deeply nested IFs with IFS (Excel 2019+, Google Sheets): =IFS(A2>=90,"A",A2>=80,"B",A2>=70,"C",TRUE,"F"). IFS is easier to read and maintain.
- 5For value-based lookups (grades, tax bands, tiered prices), consider VLOOKUP with a sorted table and approximate match, which is far more maintainable than a nested IF.
Stop hunting errors by hand.
Upload your spreadsheet and the Auditor flags every Nested IF and broken formula at once — or paste this one formula and get the fix explained.
Frequently asked
How many IFs can I nest?
Modern Excel and Google Sheets support up to 64 levels of nesting. Excel 2003 and earlier allow only 7. If you are hitting the limit, use IFS, SWITCH, or a lookup table instead.
Is IFS better than nested IF?
Yes for most multi-branch scenarios. IFS takes condition-value pairs without nesting: =IFS(A2>=90,"A",A2>=80,"B",TRUE,"F"). It is easier to read, audit, and extend.
My nested IF returns FALSE for some values — why?
The final branch is missing a default (else) value. Add a catch-all as the last argument: the innermost IF should have three arguments, with the third being the default result.
More on IF, IFS & nested conditions
See all →How-to
How to hide #N/A and other formula errors
Replace #N/A, #DIV/0!, and other errors with a blank or a friendly message using IFERROR or IFNA. Works in Excel and Google Sheets.
How-toHow to sum if a cell contains specific text
Use SUMIF with wildcard criteria (*text*) to sum values in a column whenever a related column contains a specific substring.
How-toHow to write a nested IF formula
Chain multiple IF functions inside each other to test several conditions and return different results for each scenario.
How-toHow to write an IF with multiple conditions
Combine IF with AND or OR to test multiple conditions simultaneously and return a result based on whether all or any are true.
How-toHow to combine IF and AND
Use IF with AND to return a value only when all specified conditions are simultaneously true in Excel or Google Sheets.
How-toHow to combine IF and OR
Use IF with OR to return a value when at least one of several conditions is true in Excel or Google Sheets.