Multiple IF Statements in Excel: Nested IF & IFS (2026) | Intellisoft Singapore

How to Use Multiple IF Statements in Excel (Nested IF & IFS)

To use multiple IF statements in Excel, nest one IF inside the value-if-false argument of another — =IF(test1, result1, IF(test2, result2, result3)) — so Excel checks each condition in turn and returns the first match. For four or more conditions, the IFS function does the same job with cleaner syntax. Use IF with AND or OR when one result depends on several conditions at once.

The IF function is the one most people learn first, and use most. A single IF answers one yes-or-no question. Is this value over budget? Did this student pass? Is this order local or overseas? The trouble starts when one question is not enough. You don’t just want pass or fail. You want A, B, C, D, or F. You don’t just want local or overseas. You want five shipping bands. That is the moment you need the Excel multiple IF function, and there is more than one way to do it.

I’ve taught this in Excel classes across Singapore for 24 years, and the IF function is where most people first feel like they’re really programming the spreadsheet rather than just typing into it. Don’t worry if it looks intimidating at first. We will make sense of it together, step by step. This guide walks through nested IF statements, the newer IFS function, and combining IF with AND and OR. It also covers the mistakes that quietly break these formulas, and a simple rule for knowing when to stop nesting and reach for something else.

What does a nested IF statement do in Excel?

A nested IF statement is a formula that places one IF function inside another, so Excel can test several conditions and return a different result for each one. A single IF can only return two results: one for true, one for false. By putting a second IF in the place where the first IF’s “false” result would go, you hand Excel a fresh question to ask whenever the previous answer was no.

Think of it like a security guard at the office lobby. The first question is “Are you staff?” If yes, you’re in. If no, the guard asks a second question: “Are you a registered visitor?” If no to that, a third question. Each “no” passes you down the line to the next checkpoint. A nested IF works exactly the same way. The conditions are checked one after another, top to bottom, and Excel returns the result of the first condition that is true. Once it finds a match, it stops looking.

That first-match-wins behaviour is the single most important thing to understand, because it explains almost every nested IF that returns the wrong answer. Nested IFs are useful whenever you need to sort values into more than two groups: grades, performance bands, commission tiers, tax brackets, shipping zones. They keep all the logic in one cell instead of spreading it across helper columns.

The basic IF function — your foundation

Before you stack IF functions, get comfortable with one. The IF function has three parts:

=IF(logical_test, value_if_true, value_if_false)

• logical_test — the condition you are checking, such as B2>50.
• value_if_true — what Excel returns if the test is true.
• value_if_false — what Excel returns if the test is false.

A simple example checks whether a sales figure has hit its target:

=IF(B2>=10000, "Target met", "Below target")

If the value in B2 is 10,000 or more, the cell shows “Target met”. Anything less shows “Below target”. Two outcomes, one question. Text results always go in double quotes. Numbers never do. Keep that rule in your back pocket, because it becomes a common source of errors once formulas get longer.

Everything that follows is just this same function, repeated. Once you can read one IF, you can read fifty. They only look intimidating because of the parentheses.

How to write multiple (nested) IF statements, step by step

The classic example is converting test scores into letter grades. There are five outcomes (A, B, C, D, F), so a single IF won’t do. Here is how to build the nested formula:

1. Start with the highest condition. Ask the first question: is the score above 89?

   =IF(D2>89, "A", ... )

2. Replace the false slot with the next IF. If the score is not above 89, ask the next question inside the false argument:

   =IF(D2>89, "A", IF(D2>79, "B", ... ))

3. Keep nesting for each remaining band:

   =IF(D2>89, "A", IF(D2>79, "B", IF(D2>69, "C", IF(D2>59, "D", ... ))))

4. End with the final catch-all. The last false argument is the result when nothing else matched:

   =IF(D2>89, "A", IF(D2>79, "B", IF(D2>69, "C", IF(D2>59, "D", "F"))))

5. Close one parenthesis for every IF you opened. Four IFs, four closing brackets at the end.

Read it back in plain English: if the score is over 89 give an A; otherwise, if it is over 79 give a B; otherwise, if it is over 69 give a C; otherwise, if it is over 59 give a D; otherwise give an F. Each “otherwise” is a nested IF picking up where the last one left off. How good is that? You’ve just put five outcomes into one cell.

Notice the order. Highest band first, working down. Because Excel stops at the first true result, you must arrange the conditions so the most specific or highest one is tested before the broader ones. Reverse that order and the formula breaks, which is exactly the trap in the next section.

A real worked example: commission and appraisal tiers

Grades are tidy, because the bands rarely change. Real office work is messier. Take a sales commission structure, the kind many Singapore sales teams run on:

The nested IF, with revenue in cell C9, reads:

=IF(C9>15000, 20%, IF(C9>12500, 17.5%, IF(C9>10000, 15%, IF(C9>7500, 12.5%, IF(C9>5000, 10%, 0)))))

The same pattern works for HR appraisal bands, CPF-related thresholds, or any tiered figure. The structure never changes. Only the numbers and the results do.

Now the trap. Suppose someone builds the same formula but lists the conditions from lowest to highest:

=IF(C9>5000, 10%, IF(C9>7500, 12.5%, IF(C9>10000, 15%, IF(C9>12500, 17.5%, IF(C9>15000, 20%, 0)))))

A staff member with S$13,000 in revenue should earn 17.5%. Instead the formula pays out 10%. Why? Because 13,000 is greater than 5,000, the very first test is true, Excel stops there, and every richer tier is ignored. Nothing turns red. No error pops up. The formula simply, quietly, pays the wrong commission. And this is the kind of mistake your boss will not be happy about, because nobody spots it until payroll or finance does, and by then the cheques have gone out. After 24 years I still see this one trip people up. Order your conditions from the most extreme inward, every single time.

Want your team to build formulas like this with confidence instead of trial and error? Intellisoft’s Excel training in Singapore covers IF logic, lookups, and the formula-auditing habits that catch exactly these silent errors before they reach payroll.

The IFS function — the modern fix for deep nesting

Once you pass three or four conditions, nested IFs get hard to read and easy to break. The IFS function was built to fix this. It takes condition-and-result pairs one after another, with no nesting and no pile of closing brackets:

=IFS(logical_test1, value1, logical_test2, value2, logical_test3, value3, ...)

The grades example becomes:

=IFS(D2>89, "A", D2>79, "B", D2>69, "C", D2>59, "D", TRUE, "F")

Read it as a list. If over 89 then A, if over 79 then B, and so on down the line. The last pair, TRUE, "F", is the catch-all. TRUE always counts as true, so it acts like the final “otherwise”. Leave it out and a value that matches none of your conditions returns an #N/A error, so always add a TRUE default unless you actually want that error.

If a nested IF is like Russian dolls, one tucked inside the next, IFS is like a numbered checklist on a clipboard. Same questions, laid out flat where you can read every line. It still checks conditions top to bottom and still stops at the first true result, so condition order matters here exactly as it does with nested IF. The difference is purely readability. IFS is available in Excel 2016 and later, and in Microsoft 365. If you need the file to open in an older version, stick with nested IF.

IF with AND / OR — multiple conditions in one test

Nesting handles many separate outcomes. AND and OR handle one outcome that depends on several conditions at once. They answer different questions, and mixing them up is common.

Use AND when every condition must be true:

=IF(AND(B2="Excellent", C2>5), "Bonus", "No bonus")

This awards a bonus only when the rating reads “Excellent” and the person has clocked more than 5 years of service. If either part fails, no bonus.

Use OR when any one condition is enough:

=IF(OR(B2="Manager", B2="Director"), "Eligible", "Not eligible")

Here, holding either title makes the person eligible. Only one needs to be true.

You can combine these with nesting too. A two-tier bonus that depends on both performance and tenure might read:

=IF(AND(B2="Excellent", C2>5), D2*10%, IF(AND(B2="Good", C2>3), D2*5%, "No bonus"))

This is the part the official Microsoft documentation parks on a separate page, which is why so many people learn nested IF without ever learning that AND and OR exist. They are often the simpler answer. When a manager says “give the bonus only to top performers who have been here a while”, that is an AND, not a tower of nested IFs.

Common nested-IF mistakes and how to debug them

Most nested IF problems come from the same short list. Run through it whenever a formula misbehaves:

• Conditions in the wrong order. Excel returns the first true result, so a broad condition placed before a narrow one swallows it. Order from most extreme inward. This is the single most common cause of “right formula, wrong answer”.
• Unbalanced parentheses. Every IF needs a matching closing bracket. Four IFs means four ) at the end. When you edit a formula, Excel colour-codes matching pairs and briefly highlights the partner when you type a closing bracket. Use those cues.
• Numbers wrapped in quotes. =IF(B2>"249", ...) will not behave, because "249" is text, not the number 249. Quote text results. Never quote the numbers you are comparing against.
• No catch-all. A nested IF without a final false value, or an IFS without a TRUE default, returns FALSE or #N/A for any value that matches nothing.
• Errors from other functions leaking through. If your IF wraps a VLOOKUP or a division, a missing lookup or a zero can throw #N/A or #DIV/0!. Wrap the formula in IFERROR to show a friendly message instead: =IFERROR(your_formula, "Check input").

When a long formula misbehaves and you cannot see why, use Evaluate Formula (Formula tab, Formula Auditing group). It steps through the calculation one piece at a time and shows you the exact point where Excel takes the wrong branch. Think of it as reading the formula out loud on your behalf. Breaking a giant formula into smaller test formulas in spare cells works just as well. The same disciplined checking pays off across advanced Excel data analysis, where one wrong branch can skew an entire dashboard.

When to stop nesting: a simple decision rule

You can nest 64 IFs. You almost never should. Here is a rule of thumb for picking the right tool:

• Two or three conditions → nested IF is fine and perfectly readable.
• Four or more conditions, all comparing the same value → use IFS. Cleaner, fewer brackets, easier to maintain.
• Matching a value against a lookup table that changes (price lists, rate cards, postal zones) → use VLOOKUP or XLOOKUP with a reference table. Update the table, never touch the formula.
• Matching one value against a fixed set of exact options → use SWITCH, or CHOOSE for numbered positions.

The deciding question is maintenance. Who updates this in six months, and how easily? A nested IF hides its logic inside the formula bar. A lookup table puts the logic in plain cells anyone on the team can read and edit. If the conditions or values are likely to change, get them out of the formula and into a table. Teams that handle a lot of people data lean on the same habit in our Excel course for HR professionals, where appraisal and headcount tables change every cycle.

Here is my honest opinion after teaching this for years: anyone can learn Excel formulas in a week. The IF function is not the hard part. The hard part is the judgement of choosing the simplest tool that still does the job, so the colleague who inherits your spreadsheet next year doesn’t curse your name. Build it so the next person can read it.

If you would rather build these formulas with a trainer beside you than learn by trial and error, a hands-on Excel class walks through IF logic, lookups, and the auditing habits that catch silent errors before payroll does.

Frequently asked questions

How many IF statements can you nest in Excel?
Excel 2007 and later allow up to 64 nested IF functions in a single formula. Excel 2003 and earlier allowed only 7. The technical limit is far higher than the practical one. Past three or four levels, formulas get hard to read and easy to break, so switching to IFS, VLOOKUP, or SWITCH is usually the smarter move long before you reach 64. Just because you can stack 64 doesn’t mean your future self will thank you for it.

Nested IF vs IFS — which should I use?
Use nested IF for two or three conditions, or when the file must open in older Excel versions that lack IFS. Use IFS for four or more conditions, because it lists each condition-and-result pair on one line and removes the stacked parentheses that cause most errors. IFS needs Excel 2016 or later, or Microsoft 365. Both check conditions in order and stop at the first true result, so the logic is identical. Only the readability differs.

Can I check multiple conditions without nesting IFs?
Yes. Wrap your conditions in AND when all of them must be true, or OR when any one is enough, for example =IF(AND(B2="Excellent", C2>5), "Bonus", "No bonus"). For many distinct outcomes rather than a single combined test, IFS, VLOOKUP, or SWITCH avoid nesting altogether and are far easier to maintain. Reach for AND/OR first whenever a single result depends on a couple of conditions.

Why is my nested IF returning the wrong value?
Almost always condition order. Excel reads left to right and stops at the first condition that is true, so a broad test placed before a narrow one is reached first and wins. Arrange conditions from the most extreme inward. The other usual suspects are unbalanced parentheses and numbers accidentally wrapped in quotes. Use Evaluate Formula to watch exactly where the logic takes the wrong turn.

How do I use IF with 5 or more conditions?
You can keep nesting IF functions up to 64 levels, but with five or more conditions IFS is far easier to build and read. Write each condition and its result as a pair, like =IFS(C2>89,"A", C2>79,"B", C2>69,"C", C2>59,"D", TRUE,"F"), and finish with TRUE as the catch-all so unmatched values don’t return an error. If the conditions map neatly to values in a table, a VLOOKUP is cleaner still.

I hope you found this useful. Open a blank sheet, type in a few test scores, and build the grades formula one IF at a time until it returns the right letter. Do give it a try. Once you’ve done it yourself once, multiple IF statements stop feeling like magic and start feeling like a tool you reach for without thinking. For a broader view of the formulas finance and ops teams rely on, see our guide to essential Excel formulas every finance pro needs, or learn a cleaner alternative to deep nesting in how to use VLOOKUP and XLOOKUP in Excel.