Nested IF in Excel: Multiple Conditions Without the Mess
How to write nested IF formulas in Excel for grades, tiers and commissions, why order matters, and when IFS or a lookup table is the cleaner choice.
By Ayoub Farid · · 2 min read
A single IF chooses between two results. When you need three or more (grades, price tiers, commission bands) you can put an IF inside another IF. That's a nested IF.
Example: grades
| Score | Grade |
|---|---|
| 90+ | A |
| 80–89 | B |
| 70–79 | C |
| below 70 | F |
=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C","F")))Read it left to right: if 90 or more, A. Otherwise, if 80 or more, B. Otherwise, if 70 or more, C. Otherwise F.
Why the order matters
Excel stops at the first true test. If you write the lowest threshold first:
=IF(B2>=70,"C",IF(B2>=80,"B","A"))A score of 95 is ≥70, so it returns "C". Always test from the highest threshold down (or the lowest up, using <).
Counting brackets
Each IF opens one bracket, so a three-level nested IF ends with ))). Excel colours matching brackets while you type, which helps. If you get an error about missing brackets, count the IFs and count the closing brackets.
Cleaner option 1: IFS
In Excel 2019, Microsoft 365 and Google Sheets:
=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",TRUE,"F")TRUE at the end acts as "everything else". See IFS.
Cleaner option 2: a lookup table
For many bands, put the thresholds in a table and look them up. This is easier to change later.
| E | F | |
|---|---|---|
| 1 | Min score | Grade |
| 2 | 0 | F |
| 3 | 70 | C |
| 4 | 80 | B |
| 5 | 90 | A |
=VLOOKUP(B2,$E$2:$F$5,2,TRUE)With TRUE (approximate match), VLOOKUP finds the largest threshold that's not above the score. The table must be sorted ascending. More in VLOOKUP.
Example: commission tiers
=IF(B2>=10000,B2*8%,IF(B2>=5000,B2*5%,0))Better: put the rates in cells and lock them with $ so you can change them without editing every formula.
Practise it
Grades are a classic job-test task. Try building a full grade calculator. IF and nested decisions are part of Cellio's upcoming "Decisions" unit.
Frequently asked questions
How do I use multiple IF conditions in Excel?
Nest them: put the next IF in the value_if_false part of the previous one, testing the highest threshold first.
What can I use instead of nested IF?
IFS in newer Excel and Google Sheets, or a lookup table with VLOOKUP/XLOOKUP for many bands.
How many nested IFs are allowed?
Excel allows 64 levels, but anything over three or four is hard to maintain.
Why does my nested IF return the wrong grade?
The tests are probably in the wrong order. Test the strictest condition first.