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 in Excel for the web or Excel for Microsoft 365. If you’re using an older version of Excel, you may not be able to access some of the functions.
1
INDEX With XMATCH: Perform Versatile Lookups
One of Excel’s most-used and best-loved function pairings has long been INDEX with MATCH. However, combining INDEX with XMATCH, 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.
The INDEX syntax is as follows:
=INDEX(a,b,c)
where
- a is the range,
- b is the row number, and
- c is the column number.
So, in this example, if I wanted to find Nathaniel’s total score, I would type:
=INDEX(T_Scores,5,6)
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 argument b defeats 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.
The syntax for XMATCH is as follows:
=XMATCH(a,b,c,d)
where
- a is the item to look up,
- b is the range to search,
- c is the match type (0 = exact match (default); -1 = exact match or next smallest item; 1 = exact match or next largest item; 2 = a wildcard match), and
- d is the search mode (1 = first to last (default), -1 = last to first, 2 = binary search where b is in ascending order, -2 = binary search where b is in descending order).
So, now, the whole formula reads:
=INDEX(T_Scores,XMATCH(H2,T_Scores[Player],0,1),6)
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:
=INDEX(T_Scores,XMATCH(H2,T_Scores[Player]),6)
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 is hardcoded 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:
=INDEX(T_Scores,XMATCH(H2,T_Scores[Player]),XMATCH(I2,T_Scores[#Headers]))
Now, I’ve created a two-way lookup where 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.
2
IF With AND and OR: Return a Value Based on Multiple Conditions
On its own, Excel’s IF function applies 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 the AND and OR functions can help.
The syntax for IF is as follows:
=IF(a,b,c)
where
- a is the logical test (the cell or cells you want to evaluate, and the condition you want to set),
- b is the value to return if the condition is met, and
- c is the value to return if the condition is not met.
In this example, typing:
=IF([@ExpYears]>5,"Y","N")
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 an Excel 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’ experience and the 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:
=AND(a,b)
where
- a is the first logical test, and
- b is the second logical test—and you can have up to 255 in total.
So, the combined formula is as follows:
=IF(AND([@ExpYears]>5,[@Cert]="Gold"),"Senior","Junior")
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 if all the criteria are met. However, you can also return a positive result if at least one of the criteria is met.
For example, here, an employee has the potential to be a manager if they’ve had at least 10 years’ experience or have the gold certification.
To do this, I need to nest the OR function within the IF formula:
=OR(a,b)
where
- a is the first logical test, and
- b is the second logical test—and you can have up to 255 in total.
So, here’s the formula I’ll enter into cell D2:
=IF(OR([@ExpYears]>=10,[@Cert]="Gold"),TRUE,FALSE)
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 can turn 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.
3
UNIQUE With FILTER: Return a Refined Array Without Duplicates
Excel’s UNIQUE function returns a list of unique values in a range, while the FILTER function lets you filter a range based on conditions you set.
Let’s start with the FILTER function. Here’s the syntax:
=FILTER(a,b,c)
where
- a is the range to filter,
- b is the range and condition defining the filter, and
- c is what to return if the filter doesn’t return any values.
So, for example, typing:
=FILTER(T_Shops[Manager],T_Shops[Type]=F1,"No result")
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.”
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:
=UNIQUE(a,b,c)
where
- a is the range from which the unique values will be returned,
- b is TRUE if you want to return unique columns, or omitted if you want to return unique rows, and
- c is TRUE if you want to return all unique values that occur exactly once, or omitted if you want to return all unique values that occur any number of times.
Here, typing:
=UNIQUE(T_Shops[Manager])
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:
=UNIQUE(FILTER(T_Shops[Manager],T_Shops[Type]=F1))
Go one step further and wrap the UNIQUE-FILTER combination inside the SORT function to reorder the result.
4
EOMONTH 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—with SEQUENCE—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, make sure all the cells where the dates will go contain date 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 of date-related serial numbers, rather than dates, in the result.
The EOMONTH function is straightforward, requiring only two arguments:
=EOMONTH(a,b)
where
- a is the start date, and
- b is the number of months before or after the start date, with a positive number returning a date in the future, and a negative number returning a date in the past.
Here, typing:
=EOMONTH(C1,B2)
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:
=SEQUENCE(a,b,c,d)
where
- a is the number of rows to return,
- b is the number of columns to return,
- c is the first number of the sequence, and
- d is the increment between each value in the sequence.
For example, typing:
=SEQUENCE(10,1,1,2)
into cell A1 returns a 10-row, one-column sequence of odd numbers starting at 1.
The SEQUENCE function produces a dynamic array, so make sure 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 argument b (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:
=EOMONTH(A1,SEQUENCE(11))
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:
=EOMONTH(A1,SEQUENCE(6,,,2))
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:
=EOMONTH(A1,SEQUENCE(,6,,2))
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 about different functions that work well together in Excel. However, the function you nest can be the same as the primary function driving a formula. For example, when using the XLOOKUP function to retrieve a value from a certain row, you can nest an additional XLOOKUP to make your choice of variables more dynamic, creating a powerful two-way lookup.
