These two new functions complete the set of filtering functions that started with SUMIFS, COUNTIFS and AVERAGEIFS.

• MAXIFS Returns the largest value in a list of numbers that satisfies multiple conditions
• MINIFS Returns the smallest value in a list of numbers that satisfies multiple conditions

Tired of typing complicated, nested IF formulas? They are difficult to do but even harder to comprehend. Both IFS and SWITCH make the iterative process much easier and LET eliminates repetition. 

• IFS tests values based on multiple conditions in a specific order. If passed, the result is returned. You can include an else “catch all” clause if none of the conditions are met
• SWITCH is similar to IFS, but this function evaluates an expression against a list of values in order, and returns the first matching result. If no results match, the “else” is returned
• When you enter the same expression multiple times in a formula, Excel calculates the result multiple times. LET allows you to store intermediate values as variables. The result is simpler and more efficient formulas

If you’ve ever suffered the delights of lookup formulas using Excel functions like VLOOKUP, HLOOKUP, MATCH and INDEX, then you’re going to love these new functions.

• XLOOKUP versus VLOOKUP
• XMATCH versus MATCH
• Making approximate, exact and partial matches
• Returning 12 months of data with one XLOOKUP
• A Two-Way lookup with XLOOKUP

Excel Dynamic Array functions fundamentally change the way we work with Excel formulas. We review how all formulas are affected by the new Excel calculation engine.

• Spill ranges and handling SPILL errors
• Using the hash sign (#), the new array reference notation
• Using the @ sign, the implicit intersection operator
• Combining functions
• Referencing spilled data ranges in other Excel sheets

Complicated text formulas incorporating ampersands and the CONCATENATE function are the bane of our life. Not any more!

• CONCAT This new function is like CONCATENATE, only better. It supports range references as well as cell references
• TEXTJOIN And this one is even better because it also lets you control delimiters and empty cells. Works wonders when preparing data for CSV format
• TEXTSPLIT to split text based on multiple delimiters. Grab the part of text before a delimiter with TEXTBEFORE and after a delimiter with TEXTAFTER
• Use ARRAYTOTEXT and VALUETOTEXT to easily manipulate ranges and arrays

Tediously copying and pasting data is no fun. And now there’s no need, let these functions do it for you.

• TOROW and TOCOL to combine data from multiple ranges into a single row or a single columns.
• CHOOSECOLS and CHOOSEROWS to select which columns and rows from a range you want returned.
• WRAPCOLS and WRAPROWS to take horizontal or vertical data and create multiple columns or multiple rows from it.
• The TAKE and DROP functions let you pick which part of a table you want returned. You can keep the first top rows or the last rows. Combine this with the SORT function to get the top results of your dataset.
• Use the EXPAND function to expand a range to a certain number of columns or rows.