Excel Tutorials
Test more than one condition at once, or chain several outcomes together, instead of writing separate formulas for each scenario. This lesson uses the Event Planner workbook from Excel Foundations, free to download below.
Follow along in the same file used in the video. No email required.
A single IF only handles two outcomes, true or false. Nesting means putting another IF inside the false (or true) branch of the first one, letting you test a whole chain of conditions and return a different result for each. Combined with AND and OR, you can also test multiple conditions within a single IF, rather than needing separate formulas stacked on top of each other.
It's one of the most useful formula skills in Excel, since real-world logic is rarely just one true/false question, it's usually several conditions that all matter together.
Step-by-step, matching the video above.
=IF(A2>100,"High","Low") returns one of two results based on a single condition.
=IF(A2>100,"High",IF(A2>50,"Medium","Low")) now returns three possible outcomes instead of two, by putting another IF where "Low" used to be.
Each additional IF goes inside the previous one's false branch, but more than 3 or 4 levels deep gets hard to read, that's usually a sign to consider IFS instead (see below).
=IF(AND(A2>50,B2="Approved"),"Ready","Not Ready") only returns "Ready" if both conditions inside AND are true.
=IF(OR(A2="Urgent",A2="High"),"Flag","Normal") returns "Flag" if either condition is true.
=IF(AND(A2>50,OR(B2="Approved",B2="Pending")),"Ready","Not Ready") nests OR inside AND for more layered logic.
Check what happens at the exact boundary numbers (like exactly 50 or 100) to make sure the comparison operators (>, >=) match what you actually intended.
| A: Budget Spent | =IF(A>100,"High",IF(A>50,"Medium","Low")) |
|---|---|
| $35 | Low |
| $75 | Medium |
| $150 | High |
| $50 | Low |
| $100 | Medium |
Notice $50 returns "Low" and $100 returns "Medium", not "High". The formula checks >100 first (strictly greater than), so a value of exactly 50 or exactly 100 falls into the next condition down rather than the one it might look like it should match.
| A: Amount | B: Status | =IF(AND(A>50,B="Approved"),"Ready","Not Ready") |
|---|---|---|
| $75 | Approved | Ready |
| $75 | Pending | Not Ready |
| $25 | Approved | Not Ready |
Only the first row satisfies both conditions inside AND. The second row has the right amount but the wrong status, the third has the right status but too small an amount, AND requires every condition to be true at once.
Once you're nesting more than 2 or 3 IF statements, IFS is usually easier to read and less error-prone than counting parentheses.
=IFS(A2>100,"High",A2>50,"Medium",TRUE,"Low") lists each condition and its result as pairs, no nesting or extra closing parentheses required.
Ending with TRUE as the final condition acts as a catch-all "else" for anything that didn't match an earlier condition.
IFS needs a newer version of Excel (2019 or Microsoft 365). If compatibility with older files matters, nested IF still works everywhere.
Each nested IF adds one more closing parenthesis at the very end. Miscounting is the most common error in a deeply nested formula.
=IF(A2>50,"Medium",IF(A2>100,"High","Low")) will never actually return "High", since anything over 100 already satisfied the first >50 condition. Order conditions from most to least restrictive.
AND and OR return a plain TRUE or FALSE on their own, they don't return custom text unless wrapped inside an IF.
Layered logic shouldn't live inside a wall of parentheses.
Free 14-day trial. No credit card required.
Place the second IF where a result would normally go, like =IF(A2>100,"High",IF(A2>50,"Medium","Low")).
Excel technically allows up to 64, but more than 3 or 4 becomes hard to read and maintain, IFS is usually a cleaner choice at that point.
AND requires every condition to be true. OR only requires at least one condition to be true.
Yes, one can be nested inside the other, like =IF(AND(A2>50,OR(B2="Approved",B2="Pending")),"Ready","Not Ready").
IFS lists each condition and result as pairs without nesting, like =IFS(A2>100,"High",A2>50,"Medium",TRUE,"Low"), generally easier to read than deeply nested IF.
Conditions are usually checked in order from left to right. If an earlier, broader condition catches a case meant for a later one, order your conditions from most to least restrictive.
This is almost always a missing or extra closing parenthesis, count carefully, each nested IF adds one more ) at the very end.
Every business starts with a spreadsheet. Updoot is where you scale past it, layered logic runs automatically, no formula required.
Start Your Free Trial →