Quick Links
Functions are the driving force of any Microsoft Excel spreadsheet, generating a single value or an array of results based on certain arguments you input. However, combining—or nesting—functions allows you to take advantage of more than one function’s capabilities at the same time.
This article covers both legacy and modern Excel functions, all of which are available inExcel for the webor Excel for Microsoft 365. If you’re using an older version of Excel, you may not be able to access some of the functions.
Microsoft 365 Personal
Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.
1INDEX With XMATCH: Perform Versatile Lookups
One of Excel’s most-used and best-loved function pairings has long beenINDEX with MATCH. However, combiningINDEXwithXMATCH, the modern-day version of its less versatile predecessor, gives you even more options.
In short, the INDEX function extracts a value from a dataset, with the XMATCH function serving as the navigator to the correct row and/or column. This combination can search in any direction, works with vertical and horizontal datasets, and can even return whole columns or rows.
where
So, in this example, if I wanted to find Nathaniel’s total score, I would type:
because Nathaniel is in the fifth row of the T_Scores table, and his total score is in the sixth column.
However, typing the row number (5) manually for argumentbdefeats the point of a lookup altogether because, in the process of identifying Nathaniel’s row, I could have simply looked at his score. Instead, I can replace the row number “5” in the above formula with a nested XMATCH formula, which feeds the row number into the INDEX function automatically.
So, now, the whole formula reads:
where the nested XMATCH formula replaces the manually inserted row number by identifying the row in the Player column of the T_Scores table where the value in H2 is found.
In fact, I could have omitted the final two arguments from the XMATCH formula, since exact match (0) and a top-to-bottom search (1) are the default settings:
Now, when I select a different player in cell H2, the XMATCH part of the INDEX-XMATCH combination looks for the player’s name in the table, and tells the INDEX function which row that name is on. Then, the INDEX function goes to column 6, and returns the corresponding score.
Ok, that’s great, but what if I want to return a value from a different column, like in the screenshot below?
At the moment, the reference to the sixth column ishardcoded into the formula, so I would have to edit the formula to find a value from a different column. Luckily, XMATCH can come to the rescue yet again:
Now, I’ve created atwo-way lookupwhere the first nested XMATCH formula identifies the row number where the value in cell H2 can be found, and the second nested XMATCH formula identifies the column number where the value in cell I2 can be found.
I can see that this works when I select a different player in cell H2.
2IF With AND and OR: Return a Value Based on Multiple Conditions
On its own, Excel’sIF functionapplies a condition, returning one value if the condition is met and another if it’s not. However, sometimes, you might need to input more than one condition, and this is where theAND and OR functionscan help.
The syntax for IF is as follows:
In this example, typing:
into cell C2 tests whether employee A has more than five years' experience, returning “Y” if they do, or “N” if they don’t. Because the formula is anExcel table, when I press Enter, the same formula applies to each row.
Taking this principle one step further, in this second example, any employee with more than five years' experienceandthe gold certification should be labeled “Senior” in the Status column, with everyone else labeled “Junior.”
In this case, the logical test argument of the IF formula will be a nested AND formula:
So, the combined formula is as follows:
Any text in logical arguments in Excel must be enclosed in double quotes. The only exceptions to this rule are TRUE and FALSE, which we’ll come to shortly.
Thus, this is the result when I enter the formula into cell D2 and press Enter to extend it down the Status column:
In the above example, the AND function evaluates the referenced cells and returns a positive result only ifallthe criteria are met. However, you can also return a positive result ifat least oneof the criteria is met.
For example, here, an employee has the potential to be a manager if they’ve had at least 10 years' experienceorhave the gold certification.
To do this, I need to nest the OR function within the IF formula:
Here’s how the table looks when I press Enter:
Because I used Boolean values (TRUE and FALSE) in the final two arguments of the IF formula, I canturn the cells in column D into checkboxes, where TRUE is a checked checkbox, and FALSE is an unchecked checkbox.
To do this, after selecting all the cells in the ManPot column, I can click “Checkbox” in the Insert tab on the ribbon.
3UNIQUE With FILTER: Return a Refined Array Without Duplicates
Excel’s UNIQUE function returns a list of unique values in a range, while theFILTER functionlets you filter a range based on conditions you set.
So, for example, typing:
into cell F2 searches the Type column of the T_Shops table for “DIY” (the value in cell F1) and returns all the corresponding names from the Manager column of the same table. If the filter doesn’t return any values, cell F2 will display “No result.”
Since FILTER and UNIQUE aredynamic array functions, their results spill into adjacent cells, so verify there’s enough space before you type your formula. Also,dynamic array functions can’t be used in Excel tables, so your formula must be typed in a regular, unformatted cell.
However, you can see that Lucy and Vic are duplicated in the result because they manage more than one DIY shop.
This is where the UNIQUE function does its work. First, let’s look at the syntax:
Here, typing:
into cell H2 returns the names of all the people in the Managers column of the T_Shops without duplicating names that appear more than once.
Thus, because the FILTER formula I inputted earlier returned duplicated results, I can combine it with the UNIQUE function to prevent values from being duplicated. In fact, I can simply wrap the FILTER formula inside the UNIQUE function as follows:
Go one step further and wrap the UNIQUE-FILTER combination inside theSORT functionto reorder the result.
4EOMONTH With SEQUENCE: Generate a Series of Month-End Dates
Whether you’re looking to streamline financial reports, budget for the year ahead, or plan a forthcoming project, combining EOMONTH—which returns the last day of a month before or after a start date—withSEQUENCE—which lets you create a sequence of values (or in this case, dates)—is a dynamic, time-saving, error-reducing way to create a list of month-end dates.
Before you start, verify all the cells where the dates will go containdate number formatting. To do this, select the cells, press Ctrl+1 to launch the Format Cells dialog box, and choose either “Date” or “Custom” in the Category field. Otherwise, you will see a sequence ofdate-related serial numbers, rather than dates, in the result.
takes the date in cell C1 (August 15th), jumps the number of months forward or backward according to the value in cell B2 (three months forward to November), and returns the end date of the resultant month (November 30th).
However, let’s say I want to create a sequence of month-end dates after a given starting date. This is where the SEQUENCE function, which has four arguments, comes in handy:
For example, typing:
into cell A1 returns a 10-row, one-column sequence of odd numbers starting at 1.
The SEQUENCE function produces a dynamic array, so verify there’s enough room in your spreadsheet for the result to spill into adjacent cells to avoid the #SPILL! error. Also, dynamic arrays aren’t compatible with Excel tables, so the formula you type must be in a regular cell.
To combine these functions, the SEQUENCE function acts as argumentb(the number of months before or after) in the EOMONTH function.
Here, after typing the start date into cell A1, here’s the formula I need to type into cell A2 to get the remaining month-end dates for 2025:
where A1 is the cell reference of the start date, and 11 is the number of subsequent month-end dates I want to return.
Let’s say I wanted to return the month-end dates for every second month in 2025 instead. In this case, the formula in cell A2 would be:
with the first argument of the SEQUENCE formula telling Excel to return six rows of results, and the fourth argument representing a two-month increment between each value.
In a final example, instead of having the month-end date sequence running down column A, I want them to run along row 1. Thus, after typing the start date in cell A1, the formula in cell B2 needs to be:
with the second argument of the SEQUENCE formula forcing Excel to return six columns of results, and the final argument returning every other month.
This function pairing is dynamic, meaning the sequence adjusts accordingly if you change the start date. However, if you want to fix the dates once you’ve used EOMONTH and SEQUENCE to create your month-end sequence, select all the cells containing the dates (including the starting date), press Ctrl+C to copy them, and press Ctrl+Shift+V to paste the values only.
In this guide, I’ve talked aboutdifferentfunctions that work well together in Excel. However, the function you nest can be thesameas the primary function driving a formula. For example, when using the XLOOKUP function to retrieve a value from a certain row, you cannest an additional XLOOKUPto make your choice of variables more dynamic, creating a powerful two-way lookup.
