Nested IF - Build an ordered decision chain
Handle three outcomes by placing a second decision inside the first, while keeping thresholds and parentheses understandable.
By the end, you can
- Read the path through two nested decisions.
- Order overlapping thresholds from strongest to weakest.
- Choose a maintainable decision chain and fallback.
Before you start: Understand IF's TRUE and FALSE branches. IFS provides another way to write ordered rules.
In this lesson
An IF can return another IF
A nested IF places one IF inside an outcome of another. When the first test fails, a second IF can make a more specific decision instead of immediately returning one fallback. Use a short chain for ordered price tiers, service bands, or assessment categories. It is not a separate function name: the formula still uses IF.
=IF(test1, result1, IF(test2, result2, fallback))The outer FALSE branch contains a complete second IF. The final fallback covers inputs that fail both tests.
An illustrative order discount schedule
₹1,200| A | B | C | |
|---|---|---|---|
| 1 | Customer | Order value | Discount |
| 2 | Apex | ₹12,000 | ₹1,200 |
| 3 | Bright | ₹6,000 | ₹300 |
| 4 | Core | ₹4,000 | ₹0 |
| 5 | Delta | ₹10,000 | ₹1,000 |
=IF(B2>=10000,B2*0.10,IF(B2>=5000,B2*0.05,0))Apex passes the first test. Its discount is 12,000 × 0.10 = 1,200; the lower tier is not selected.
- Write the tiers in policy order: >=10,000, then >=5,000, then no discount.
- Build the inner IF for the lower tier and its zero fallback.
- Use that inner IF as the outer IF's FALSE result.
- Close both parentheses, fill down, and check ₹5,000 and ₹10,000 exactly.
Follow the other paths
=IF(B3>=10000,B3*0.10,IF(B3>=5000,B3*0.05,0))Bright's 6,000 is below 10,000 but at least 5,000. Its discount is 6,000 × 0.05.
=IF(B4>=10000,B4*0.10,IF(B4>=5000,B4*0.05,0))Core's 4,000 reaches neither threshold, so the final zero is used.
=IF(B5>=10000,"10% tier",IF(B5>=5000,"5% tier","No discount"))Delta at exactly 10,000 receives the highest tier label. Text outputs must be quoted.
Use cell-held policy inputs
In a recurring report, store thresholds and rates in clearly labelled cells. For example, replace 10000 with an absolute reference to the upper threshold and 0.10 with an absolute reference to its rate. This lets the policy change without rewriting every row. The order of the tests still matters.
The same decision chain can label assessment bands or calculate an allowance. If every tier has a separate condition/result pair, IFS may be easier to read. If many tiers change frequently, a lookup table may be more maintainable than a growing IF chain.
Order, boundaries, and complexity
- A high amount also satisfies the lower threshold. Testing >=5000 first would wrongly choose the 5% tier for a ₹12,000 order.
- Use >= for a policy that includes the threshold. Test just below, exactly at, and just above each boundary.
- Errors in a condition do not automatically become the final fallback. Validate unexpected text, missing amounts, and broken references separately.
- Excel permits up to 64 nested function levels, but that limit is not a design target. Keep chains short and document the business rule through clear labels and inputs.
Common mistakes and fixes
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.