Excel nested IF formulas let you test several conditions in order and return a different result for each one.
These practical examples show how to build them, check the expected outputs, and avoid common errors as your rules grow.
How a nested IF formula works
The IF function checks a condition, then returns one value when the condition is true and another when it is false.
A nested IF places another IF in one of those result positions:
=IF(condition1, result1, IF(condition2, result2, result_if_neither))Excel evaluates the conditions from left to right.
It stops at the first true condition, so put rules in an order that gives the intended result.
In the examples below, enter the formula in the indicated cell and copy it down where needed.
1. Assign a letter grade from a score
Suppose scores are in cell A2.
This formula assigns A to scores of 90 or higher, B to 80–89, C to 70–79, D to 60–69, and F to scores below 60:
=IF(A2>=90,"A",IF(A2>=80,"B",IF(A2>=70,"C",IF(A2>=60,"D","F"))))Expected results: a score of 95 returns A, 82 returns B, and 54 returns F.
The descending thresholds make separate upper bounds unnecessary: after Excel rules out 90 and above, a score of 80 or more must be in the B range.
For a blank score cell, this formula returns F because a blank is treated like zero in this comparison.
To keep blank rows empty, add a check at the start:
=IF(A2="","",IF(A2>=90,"A",IF(A2>=80,"B",IF(A2>=70,"C",IF(A2>=60,"D","F")))))2.
Calculate a tiered commission rate
If sales in B2 qualify for rates of 2% below $1,000, 5% from $1,000 to $4,999.99, and 8% at $5,000 or more, use:
=IF(B2>=5000,8%,IF(B2>=1000,5%,2%))Expected rates: $750 returns 2%, $2,500 returns 5%, and $5,000 returns 8%.
To calculate the commission amount instead of displaying a rate, multiply the selected rate by sales:
=B2*IF(B2>=5000,8%,IF(B2>=1000,5%,2%))This example applies one rate to all sales in the tier.
A progressive commission, where each portion of sales has a different rate, needs a different calculation.
3. Label a value with a status
To classify inventory in C2 as “Out of stock” at zero, “Low stock” below 10, or “In stock” otherwise, enter:
=IF(C2=0,"Out of stock",IF(C2<10,"Low stock","In stock"))Expected results: 0 returns “Out of stock,” 6 returns “Low stock,” and 10 returns “In stock.” If negative values are possible and should be flagged separately, test for them first:
=IF(C2<0,"Check quantity",IF(C2=0,"Out of stock",IF(C2<10,"Low stock","In stock")))4.
Assign a delivery label from an order amount
Assume an order total is in D2.
Orders of $100 or more receive “Free shipping,” orders of at least $50 receive “Discounted shipping,” and smaller orders receive “Standard shipping”:
=IF(D2>=100,"Free shipping",IF(D2>=50,"Discounted shipping","Standard shipping"))Expected results: $120 returns “Free shipping,” $75 returns “Discounted shipping,” and $35 returns “Standard shipping.” If the order total might be blank, decide whether a blank should count as a small order.
To leave it unlabeled instead, use:
=IF(D2="","",IF(D2>=100,"Free shipping",IF(D2>=50,"Discounted shipping","Standard shipping")))5.
Check a date against two deadlines
With a due date in E2, classify the task as “Overdue” if the date is before today, “Due soon” if it falls within the next seven days, and “Later” otherwise:
=IF(E2<TODAY(),"Overdue",IF(E2<=TODAY()+7,"Due soon","Later"))Expected results depend on the day the workbook is opened: yesterday is “Overdue,” a date seven days from today is “Due soon,” and a date eight days away is “Later.” TODAY() updates with the current date when Excel recalculates.
A blank cell can be treated as overdue, so guard against blanks if needed:
=IF(E2="","",IF(E2<TODAY(),"Overdue",IF(E2<=TODAY()+7,"Due soon","Later")))6.
Return a value based on a text category
If F2 contains a shipping method, this formula returns its fee: $0 for “Pickup,” $5 for “Standard,” and $12 for “Express.” Any other text returns “Check method.”
=IF(F2="Pickup",0,IF(F2="Standard",5,IF(F2="Express",12,"Check method")))Excel text comparisons with the equals operator are generally case-insensitive, so “express” matches “Express.” Extra spaces or spelling differences can still prevent a match.
For data that may contain leading or trailing spaces, normalize it with TRIM:
=IF(TRIM(F2)="Pickup",0,IF(TRIM(F2)="Standard",5,IF(TRIM(F2)="Express",12,"Check method")))7.
Combine multiple conditions inside nested IF
A condition can use AND or OR.
For example, if a customer is eligible for a discount only when their order in G2 is at least $100 and their membership status in H2 is “Active,” use:
=IF(AND(G2>=100,H2="Active"),"Discount eligible","No discount")To add a separate “Review order” outcome for orders over $1,000, nest another IF while keeping the eligibility test together:
=IF(G2>1000,"Review order",IF(AND(G2>=100,H2="Active"),"Discount eligible","No discount"))An order of $1,200 returns “Review order” regardless of membership because that condition comes first.
An active member with a $150 order returns “Discount eligible.”
Common nested IF errors and fixes
- Wrong rule order: A broad condition can catch values intended for a later rule.
Put specific or higher thresholds first when they should take priority. - Missing parentheses: Each nested IF needs a closing parenthesis.
Excel’s formula bar can help locate an incomplete expression; line breaks and indentation can also make long formulas easier to inspect. - Text without quotation marks: Return labels such as
"Approved"need double quotes.
Cell references and function names do not. - Unexpected blank results: A blank cell can behave like zero in numeric comparisons.
Add an explicit blank check when empty input should produce an empty result. - Locale-specific separators: Many installations use commas between function arguments, while some use semicolons.
If Excel rejects a formula with commas, replace the argument separators with semicolons.
When to replace nested IFs with another function
Nested IF is suitable for a few clear rules.
For several exact text categories, SWITCH can be easier to read.
For ordered numeric bands or a growing set of conditions, a lookup table with XLOOKUP or VLOOKUP is often easier to maintain than a long chain of tests.
IF is available in modern Excel and older releases, including Excel 2016 and Excel 2019.
IFS is available in Excel 2019, Excel 2021, Excel 2024, and Microsoft 365; SWITCH is also supported in current perpetual and Microsoft 365 releases.
XLOOKUP is available in Microsoft 365 and newer perpetual Excel versions such as Excel 2021 and 2024, but not Excel 2016 or 2019.
Google Sheets supports nested IF, IFS, SWITCH, and XLOOKUP.
If a workbook must be shared with older Excel users, nested IF or VLOOKUP may offer broader compatibility.