The Beginner’s Guide to Nested Functions in Excel

Nesting in Microsoft Excel involves using one function as an argument for another, allowing you to perform multiple calculations at the same time. It saves you from having to use helper columns or write various formulas in various cells, keeping your worksheet tidier and more efficient.

Key Definitions

This guide uses the following three terms throughout, so take a moment to understand what they mean:

• Formula: A combination of functions, cell references, values, operators, and names that, when used together following the equals (=) sign, create a new value.
• Function: A pre-defined formula that performs a calculation using arguments in a particular order.
• Argument: An input that provides information to a function.

Nesting Functions Inside IF in Excel

If you're new to nested functions in Excel, you should start by using the IF function:

=IF(a,b,c)

where argument a is a logical test, argument b is the value to return if the test is met, and argument c is the value to return if the test isn't met. For the logical test in argument a, you can nest another function.

In this example, bonuses are paid if the team's total profit (the sum of the values in cells B2 to B6) exceeds the threshold (the value in cell E1). If the threshold is met, you want cell E2 to read Yes, and if it's not, you want it to read No. The formula to achieve this is as follows:

=IF(SUM(B2:B6)>=E1,"Yes","No")

So, how does this work?

When creating or reading formulas containing nested functions, the key is to start in the center, and work outward. Central to this formula is the sum of the values in cells B2 to B6, so this is where you need to start:

=SUM(B2:B6)

Next, you want Excel to evaluate whether this sum is equal to or greater than the value in cell E1. By default, Excel returns TRUE if the evaluation is positive, or FALSE if it's negative—also known as Boolean values:

=SUM(B2:B6)>=E1

Now, rather than return Boolean values, you want Excel to return Yes or No. Thus, you need to wrap the SUM formula inside the IF function, so that the sum calculation is the logical test (argument a):

=IF(SUM(B2:B6)>=E1,"Yes","No")

You don't have to use the SUM function inside IF. In fact, you can nest pretty much any function as the logical test. Here, the AVERAGE function calculates the average of the values in cells B2 to F2, and if the result is more than 5,000, the formula returns Yes (or No if it isn't):

=IF(AVERAGE(B2:F2)>5000,"Yes","No")

You can then double-click the fill handle in the bottom-right corner of cell G2 to expand the formula to the remaining cells in column G.

This time, the OR function opens up two options for the logical test of the IF function. In other words, if the value in B2 is Ordered or In transit, the formula returns In progress. Otherwise, it returns Completed:

=IF(OR(B2="Ordered",B2="In transit"),"In progress","Completed")

Once again, double-clicking the fill handle applies the formula to the remaining rows in the dataset.

Other Examples of Nesting in Excel

Once you start experimenting with other functions, the power of nesting quickly becomes clear.

Nesting FILTER Inside UNIQUE

In this case, your aim is to extract the names of everyone who has won a prize in New York.

In other words, you want to use the FILTER function to reproduce a filtered version of column C. To do this, type:

=FILTER(C2:C15,B2:B15=F1)

into cell E2 and press Enter. C2:C15 is the array you want to filter (people's names), and B2:B15=F1 tells Excel to only include the values where the corresponding value in column B equals the value in cell F1 (New York).

However, as you can see in the screenshot above, one of the names is repeated because that person has won twice in New York. So, you need to nest the FILTER function inside UNIQUE to return each name only once:

=UNIQUE(FILTER(C2:C15,B2:B15=F1))

The key in this process is to force Excel to perform the main calculation first, then use the result of this formula to drive another.

Nesting XMATCH Inside INDEX

One of Excel's most popular function pairings is INDEX and XMATCH, which work together to retrieve and extract a value from a dataset.

Here, after entering a player's ID into cell F2, you want to return their score in cell G2.

To do this, you can use the INDEX function:

=INDEX(a,b,c)

where a is the array to scan, b is the row number, and c is the column number.

For argument b, because the row changes depending on the player ID you enter into cell F2, you need to nest the XMATCH function, which searches for a specified item and returns its relative position. And because the best approach to nesting in Excel is to start with the nested functions, this is where you should begin. So, in cell G2, type:

=XMATCH(F2,A2:A6)

When you press Enter, this formula correctly returns 4, because player D is on the fourth row of the range A2 to A6.

Now, this XMATCH formula can act as argument b of the INDEX function, so you now need to wrap the rest of the INDEX arguments around XMATCH:

=INDEX(a,XMATCH(F2,A2:A6),c)

Argument c is going to be 4 because you want to return the value from the fourth column (total):

=INDEX(a,XMATCH(F2,A2:A6),4)

So now, all that's required is to enter argument a, and since this is the whole array to scan, it's going to be cells A2 to D6:

=INDEX(A2:D6,XMATCH(F2,A2:A6),4)

Now, when you enter a different player into cell F2, the nested XMATCH searches for it in cells A2 to A6, and returns its relative position for argument b of the INDEX function.

If you don't always want to return the total in the fourth column, you could go one step further and nest another XMATCH formula to determine the column number:

=INDEX(A1:D6,XMATCH(F2,A1:A6),XMATCH(G2,A1:D1))

In this case, for argument c, XMATCH takes the value in cell G5, searches for it in cells A1 to D1, and returns its relative position.

Nesting MAX Inside AVERAGE

Suppose you have this spreadsheet, which contains five players' scores across three games, and your aim is to find the average of the highest scores in each.

In other words, you first need Excel to work out the maximum scores in cells B2 to B6, C2 to C6, and D2 to D6, respectively, before averaging the result. So, the MAX function needs to be nested inside the AVERAGE function.

First, set up the three MAX nestings, bearing in mind that arguments for the AVERAGE function are separated by a comma:

MAX(B2:B6),MAX(C2:C6),MAX(D2:D6)

Now, with Enter mode still activated, wrap the AVERAGE function around these nested MAX formulas:

=AVERAGE(MAX(B2:B6),MAX(C2:C6),MAX(D2:D6))

Nesting Functions Inside Themselves

So far, all the examples have nested one function inside another. However, sometimes, you might need to nest a function within itself. Confusing? Let me show you how this works using the IF function.

In this worksheet, an employee's bonus is determined by the profit they make. Specifically, profits over $25,000 result in a 10% bonus, profits over $20,000 return a 5% bonus, profits over $10,000 yield a 2% bonus, and profits over $10,000 result in a 1% bonus.

In this case, you need to nest IF to create a tiered evaluation system from highest to lowest. This is because nested IF functions evaluate from left to right, stopping at the first condition that is true. So, in cell C2, type:

=IF(B2>25000,10%

but don't press Enter yet. This first section tells Excel to evaluate cell B2, and if it's greater than $25,000 (in other words, if it evaluates the logical test as TRUE), return 10%. However, you need to tell Excel what to do if it returns FALSE. In this case, you want to check whether the value in cell B2 is greater than the second-tier value, $20,000, returning 5% if so:

=IF(B2>25000,10%,IF(B2>20000,5%

If this nested IF function returns FALSE, you want Excel to move to the next tier, so you must nest another IF function:

=IF(B2>25000,10%,IF(B2>20000,5%,IF(B2>15000,2%

Now, you need to create the lowest tier through another nested IF function:

=IF(B2>25000,10%,IF(B2>20000,5%,IF(B2>15000,2%,IF(B2>10000,1%

Since there are no more tiers, you need to tell Excel what to return if none of the nested IF functions evaluate as TRUE. In this case, you want to return 0%:

=IF(B2>25000,10%,IF(B2>20000,5%,IF(B2>15000,2%,IF(B2>10000,1%,0%

Now that all the tiers are created through nested IF functions, you can close the parentheses. There are four opening parentheses, so you need to type four closing parentheses before pressing Enter:

=IF(B2>25000,10%,IF(B2>20000,5%,IF(B2>15000,2%,IF(B2>10000,1%,0%))))

You can now double-click the fill handle to apply the formula to the remaining cells in column C.

Nesting functions in Excel allows you to take advantage of more than one function's capabilities at the same time. The more you make the most of this formula structure, the more you'll start to learn which function combinations work well to help speed up your spreadsheet workflow.

OS

Windows, macOS, iPhone, iPad, Android

Free trial

1 month

Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.