Excel Nested Formula Examples and How They Work

Excel nested formulas place one function inside another, so a calculation or test can supply the input for a larger formula. For example, with a score in A2, =IF(A2>=80,"Pass","Review") returns Pass when A2 contains 86; a genuinely nested version can test several score bands by placing another IF inside the first one.

What a nested formula means

A formula is nested when a function appears inside the parentheses of another function. The inner function is evaluated as part of the outer function’s calculation. A common pattern is to put AND or OR inside IF, use SUM to calculate a total that IF then checks, or put a lookup function inside IFERROR to provide a fallback when a lookup fails.

Nesting is different from simply writing functions side by side. In a nested formula, the result of the inner function becomes an argument for the outer one. That lets a single cell make a decision based on multiple tests, calculate a value and then classify it, or display a useful message instead of an error. Microsoft 365 for Windows is the assumed Excel version here; the basic IF, AND, OR, SUM, and IFERROR examples also work in many earlier desktop versions.

Nested IF examples for multiple outcomes

Nested IF functions are useful when a value must be assigned to one of several categories. The order matters: Excel checks the first condition, then checks the next only if the previous test was false. Put the more demanding thresholds first so a high score is not caught by a lower threshold prematurely.

With a student’s score in A2, this formula assigns a letter grade. If A2 contains 86, it returns B because the first test is false and the second is true:

=IF(A2>=90,"A",IF(A2>=80,"B",IF(A2>=70,"C","Needs review")))

The final text is the result when all three comparisons are false. That makes the formula cover every possible numeric score, including scores below 70. If the cell might contain text or be blank, decide how those cases should be handled too; a formula designed only for numbers may not express the intended policy clearly.

With a value in A2, the same structure could classify order size. For example, use descending thresholds for large, medium, and small orders, and choose a final fallback such as “Check amount.” Before copying a nested IF down a column, verify the threshold order and test values just below, equal to, and just above each boundary.

Combine IF with AND or OR

Use AND when every condition must be true, and OR when at least one condition is enough. Embedding either function in IF is one of the most readable forms of nesting because the test is separated from the result to show. AND and OR can each accept multiple logical tests.

With attendance percentage in A2 and exam score in B2, this formula marks a student as passing only when attendance is at least 90 and the exam score is at least 70. With A2 equal to 94 and B2 equal to 78, it returns Pass:

=IF(AND(A2>=90,B2>=70),"Pass","Review")

With the same cell layout, suppose a record should be reviewed if either the attendance is below 90 or the exam score is below 70. The OR version returns Review when either condition is true:

=IF(OR(A2<90,B2<70),"Review","Pass")

These two formulas express complementary rules only because they use matching cutoffs and the same two inputs. In real worksheets, write down the rule in plain language first. “Both requirements must be met” calls for AND; “any one of these warnings is enough” calls for OR. Confusing the two is a common source of results that look plausible but do not match the policy.

Use a calculation inside a decision

A nested formula can calculate a value before testing it. This is helpful when the condition concerns a total rather than a single cell. With amounts in B2:B5, the formula below adds the four amounts and checks whether the result reaches 100. If the cells contain 35, 20, 25, and 30, SUM returns 110 and the formula returns Target met:

=IF(SUM(B2:B5)>=100,"Target met","Below target")

The advantage is that the total and the decision stay together, so a separate helper cell is not required. A helper cell can still be preferable when the total is reused elsewhere or when other people need to inspect the calculation. For instance, putting the sum in one column and the status in another can make a large tracking sheet easier to audit.

For money-related worksheets, treat sample thresholds as illustrations rather than financial guidance. Check the actual contract, fees, rate terms, or other relevant figures with the lender or a qualified professional. Also format monetary inputs consistently; a formula can calculate correctly while a number is misunderstood because its currency or units are unclear.

Handle lookup errors with IFERROR

IFERROR can wrap a lookup so the worksheet displays a chosen message when the lookup produces an error. With lookup values in A2:A10, corresponding return values in C2:C10, and the item to find in E2, the formula below returns the matching value from column C. If E2 contains an item found on row 5 and C5 contains 125, the result is 125; if there is no match, the result is Not found:

=IFERROR(XLOOKUP(E2,A2:A10,C2:C10),"Not found")

XLOOKUP is available in Microsoft 365 and Excel 2021 or later. The lookup range and return range should have corresponding rows. IFERROR catches errors broadly, not just a missing match, so it can also hide a different problem in the lookup expression. If you need to distinguish a missing item from another error, diagnose the lookup directly or use a more specific approach rather than masking every error with the same message.

In older Excel versions without XLOOKUP, VLOOKUP is a common alternative. With the item to find in the first column of A2:C10, the returned value in the third column of that range, and the search key in E2, the following formula returns 125 if the matching row’s third-column value is 125; otherwise it displays Not found:

=IFERROR(VLOOKUP(E2,A2:C10,3,FALSE),"Not found")

FALSE requests an exact match. VLOOKUP searches only the first column of its table range and returns a value to its right, so the range and column number must suit the worksheet’s layout. XLOOKUP is more flexible about lookup direction, but compatibility matters if a workbook will be opened in an older Excel release.

When IFS or a helper column is clearer

A long chain of nested IF functions can be difficult to edit. IFS offers a flatter way to test several conditions in order. It returns the result corresponding to the first condition that is true, so the order still matters. With a score in A2 equal to 86, this formula returns B:

=IFS(A2>=90,"A",A2>=80,"B",A2>=70,"C",TRUE,"Needs review")

IFS is available in newer Excel releases, including Microsoft 365 and Excel 2019 or later. The final TRUE condition acts as a fallback; without a condition that catches the remaining cases, IFS can return an error when none of its tests is true. For workbooks shared with older versions, a nested IF may be more compatible.

There is no rule that every calculation must fit into one cell. Use separate helper columns when stages have different purposes, when the same intermediate value is used several times, or when a colleague needs to audit the logic. A single nested formula can be compact, but compactness is not automatically clarity. A useful test is whether someone else can explain what each decision branch does without having to mentally unravel a long expression.

Build and check a nested formula

Construct a nested formula from the inside out, then test the finished result against examples. For a multi-branch rule, this process helps catch missing cases and misplaced parentheses before the formula is copied through a dataset.

Write the rule in plain language. State the conditions and the result for each outcome. Note whether multiple conditions must all be met or whether one is enough.
Build the inner test first. For example, check whether a score meets a threshold before adding the outer IF that assigns text.
Add each branch deliberately. Put higher or more restrictive thresholds first when the logic requires it, and decide on a fallback result.
Enter the formula in the intended cell. In Excel for Microsoft 365 on Windows, select the cell, type the formula in the formula bar or directly in the cell, and press Enter. Excel on Mac uses the same formula-entry method; the exact ribbon layout can vary slightly by platform.
Test boundary cases. Check values immediately below, exactly at, and immediately above each threshold, plus blanks or unexpected text if those can occur.
Copy only after checking references. Relative references change as a formula is filled down. Use absolute references with dollar signs where a threshold or lookup range must stay fixed.

For longer formulas, line breaks and indentation can make the logic easier to inspect while editing, although Excel stores the formula as one expression. Formula separators can depend on regional settings; the examples here use commas as requested for standard English-language syntax. If Excel rejects a comma-separated formula on a particular system, check that installation’s list-separator settings rather than changing the function names at random.

Common nested formula problems

Wrong condition order: A broad condition placed before a narrower one can capture cases meant for a later branch. Arrange tests so the intended category is reached first.
Missing fallback: Nested IF and IFS logic should account for values that do not meet any stated condition. A deliberate final result is often safer than leaving an accidental gap.
Unbalanced parentheses: Every opening parenthesis needs a matching closing one. Excel often highlights parts of a formula while editing, but checking each function’s arguments is still useful.
Text and numbers do not match: A numeric value stored as text may not behave like a number in comparisons or lookups. Check the source data and its format rather than assuming the display alone tells the whole story.
Unexpected blanks: A blank cell can be treated differently from zero or an empty text string. Decide which meaning the worksheet should use, then test that case explicitly.
Errors hidden by IFERROR: A fallback message can make a sheet look tidy while concealing a misspelled reference or other formula fault. Use IFERROR only when replacing the relevant errors is genuinely intended.

When a result is wrong, break the expression into smaller parts temporarily. Evaluate the inner test or calculation on its own, confirm its result, and then put it back inside the outer function. In Excel, selecting part of a formula in the formula bar and pressing F9 can evaluate that selected portion; press Esc afterward to avoid replacing the formula with the displayed value. On many Mac keyboards, use Fn+F9 if the function keys are configured for hardware controls. Work on a copy or carefully cancel the edit if unsure.

Frequently Asked Questions

How many IF functions can I nest in Excel?
Excel supports up to 64 levels of nested functions in current versions, but that is a technical ceiling, not a recommendation. A very long chain is difficult to maintain; consider IFS, helper columns, or a lookup table when the rule has many outcomes.

Can I put AND or OR inside an IF formula?
Yes. AND and OR can serve as IF’s logical test, allowing a decision to depend on multiple conditions. Choose AND when every test must be true and OR when any one true test is sufficient.

What is the difference between a nested formula and a nested IF?
A nested formula is any function placed inside another function. A nested IF specifically places one IF inside another IF. For example, IFERROR wrapped around a lookup is nested, even though it does not contain nested IF functions.

Why does my nested formula return the wrong category?
Check the order of the tests, the threshold boundaries, and the cell values’ types. Test values at each boundary and confirm that the fallback result is reached only when all intended conditions fail.

Should I use nested IF or IFS?
IFS is often easier to read for several sequential conditions in supported Excel versions. Nested IF can work in older releases and can express more complex branching. Choose the clearest option for the people and Excel versions that will use the workbook.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.