Complete Guide to New Excel Functions: From XLOOKUP to TEXTSPLIT, TAKE, DROP, and GROUPBY

If you want to learn new Excel functions all at once, an article that simply lists function names is difficult to apply immediately at work. For functions added in Excel 365 and Excel 2024 especially, what matters more than “what is new” is whether they can make the work you already do shorter and less prone to breaking.

This guide is organized as quick fixes → principles → practical examples → precautions → troubleshooting so that even beginners can follow along. It includes copy-and-paste sample data and formulas, making it a hands-on guide you can reproduce rather than just read.

For a complete function list, see the Free Guide to All Excel Functions. This article focuses on the functions with the greatest practical impact in recent versions, organized around common workflows.

Table of Contents

1. Quick Fixes: 7 New Functions to Try Today
2. What Are New Excel Functions?
3. Create Sample Data for Practice
4. XLOOKUP and XMATCH: Transforming Lookup Tasks
5. FILTER, SORT, and UNIQUE: Speed Up Reports
6. TEXTBEFORE, TEXTAFTER, and TEXTSPLIT: Simplify Text Cleanup
7. TAKE, DROP, CHOOSECOLS, and VSTACK: Work with Array Shapes
8. LET, LAMBDA, MAP, REDUCE, and SCAN: Shorten Formulas
9. GROUPBY and PIVOTBY: Reduce PivotTable Clicks
10. Version Compatibility and Sharing Considerations
11. Troubleshooting Common Issues
12. Workplace Checklist

Quick Fixes: 7 New Functions to Try Today

1. If lookups frequently break, replace VLOOKUP with XLOOKUP first.
2. If you need only rows that meet a condition, FILTER is easier to reuse than the Filter menu.
3. If you need a list of unique values, try UNIQUE before creating a PivotTable.
4. If you need to split text before, after, or at a delimiter, use TEXTBEFORE, TEXTAFTER, and TEXTSPLIT.
5. If you need only the top 5 items, use TAKE instead of sorting and copying.
6. If you need to combine multiple tables, use VSTACK or HSTACK instead of copying and pasting.
7. If formulas are too long, use LET to name and organize intermediate calculations.

You do not need to memorize every new function at once. Most business workbooks repeatedly involve lookups, conditional extraction, text splitting, top-N extraction, table combining, and summarizing. Replacing just these six tasks with newer functions can make a noticeable difference.

What Are New Excel Functions?

Today, new Excel functions do not simply mean functions added recently. From a practical perspective, they are easiest to understand in these four groups.

The key point is that Excel has shifted from working with tables one cell at a time to working with arrays. In the past, formulas were strongly associated with returning one result in one cell. Now, it is far more natural to use one formula to spill an entire table.

Why Switch to New Excel Functions?

Business workbooks are often slow and prone to breaking not because the functions are difficult, but because older methods were forced together. For example, splitting text by nesting LEFT, RIGHT, MID, and SEARCH several layers deep, or manually fixing VLOOKUP column numbers, causes formulas to fail as soon as the structure changes.

New functions, by contrast, have clearer purposes. Their names directly describe tasks such as “extract text before,” “extract text after,” “filter rows by condition,” “select specific columns,” and “stack tables vertically.” This makes formula structures easier for beginners to understand.

Create Sample Data for Practice

Copy the data below, paste it into cell A1 in Excel, convert it to a table with Ctrl + T, and name the table tblSales.

Create an Amount header in column H and enter the formula below to add an amount column.

=[@Qty]*[@UnitPrice]

You can now reproduce most new-function examples using the same table. Because you do not need to create multiple separate example worksheets, this approach is much less confusing for beginners.

XLOOKUP and XMATCH: Transforming Lookup Tasks

Among new Excel functions, XLOOKUP is the first one you should learn. The reason is simple: finding something is one of the most common tasks in business documents. Finding a product price, an employee name by ID, a category by code, or a reference value by date are all lookup tasks.

Because traditional VLOOKUP uses a column-number approach, it can easily break when the table structure changes. For a more detailed comparison, see the XLOOKUP vs VLOOKUP comparison or the INDEX MATCH practical guide.

Example 1: Look Up a Unit Price by Product Code

Create the table below in J1:K4 and name it tblPrice.

Enter a product code in cell L2, then look up its unit price in M2.

=XLOOKUP(L2, tblPrice[Product], tblPrice[UnitPrice], "Not found")

Expected result: If you enter B in L2, M2 returns 22000.

Example 2: Find the Last Match

In practice, you often need the most recent value. Because XLOOKUP lets you control the search direction, finding the last record is easy.

=XLOOKUP("A", tblSales[Product], tblSales[Amount], "Not found", 0, -1)

This formula returns the Amount from the last row where Product is A. It is especially useful for tasks such as finding the latest shipping price, last status value, or most recent unit price.

Example 3: Use XMATCH When You Only Need the Position

If you need a position rather than a value, XMATCH is more appropriate. Combined with INDEX, it can also create flexible two-dimensional lookups.

=XMATCH("C", tblPrice[Product], 0)

The result is 3. Using this position with INDEX can create a lookup formula that is relatively resilient to table structure changes. If you need multi-criteria lookups, see the practical guide to multiple-criteria lookups.

FILTER, SORT, and UNIQUE: Speed Up Reports

In older Excel versions, you had to copy matching rows separately or use Advanced Filter. With new functions, one formula does the job. This is the biggest advantage of dynamic array functions.

Example 1: Automatically Extract Only KR Region Rows

=FILTER(tblSales, tblSales[Region]="KR", "Not found")

This formula spills all rows for the KR region. When the source table grows, the result range expands automatically. It is especially powerful for extracting items such as “only this month’s Seoul branch,” “online channel only,” or “error rows only” on a report sheet.

Example 2: Extract by Condition and Sort by Amount

=SORT(FILTER(tblSales, tblSales[Channel]="Online", "Not found"), 8, -1)

This sorts in descending order by Amount, the eighth column. In other words, it extracts only Online channel data and immediately displays it from highest to lowest sales amount. No manual filtering and sorting are needed.

Example 3: Create a List of Unique Values

=UNIQUE(tblSales[Region])

The result is a list without duplicates, such as KR, US, and EU. It is commonly used for source lists for drop-downs, summary-table row headers, and region report selection interfaces.

Example 4: A Combined Pattern

The combination beginners notice most is this single formula.

=TAKE(SORT(FILTER(tblSales, tblSales[Channel]="Online"), 8, -1), 3)

Its meaning is simple: extract Online rows → sort by Amount in descending order → return only the top 3 rows. A workflow that once required helper columns and manual sorting can be completed in one line.

TEXTBEFORE, TEXTAFTER, and TEXTSPLIT: Simplify Text Cleanup

Text processing is one of the most noticeable benefits of new Excel functions. In the past, extracting the first portion of a product code required combining LEFT and SEARCH, while splitting by a delimiter required using the Text to Columns command. Now, the functions do exactly what their names describe.

Example 1: Extract Text Before a Delimiter

Enter the formula below in cell N2.

=TEXTBEFORE(G2, "|")

If G2 contains KR|A|Starter, the result is KR.

Example 2: Extract Everything After the First Delimiter

=TEXTAFTER(G2, "|")

For the same data, the result is A|Starter.

Example 3: Split into Multiple Cells at Once

=TEXTSPLIT(G2, "|")

The results KR, A, and Starter spill into adjacent cells. This one function makes tasks such as separating region, product, and tag; email username and domain; or code, option, and color much easier.

Why TEXTSPLIT Matters

Beginners often think of the Text to Columns command first, but the menu-based method changes the original structure and does not update automatically after a one-time transformation. With TEXTSPLIT, the results change whenever the source cell changes. In other words, it creates a recalculating structure rather than a static transformation.

You can also quickly review the overall flow of text and array functions in the A-to-Z Function Guide.

TAKE, DROP, CHOOSECOLS, and VSTACK: Work with Array Shapes

This section covers the new Excel functions that can change the way you work once you become familiar with them. You can use formulas to bring in only part of the data, select specific columns, and combine multiple tables.

Example 1: View Only the First 5 Rows — TAKE

=TAKE(tblSales, 5)

This returns only the first 5 rows. Use a negative number to take rows from the end.

=TAKE(tblSales, -3)

This returns the last 3 rows. It is useful for viewing the latest 3 log entries or the most recent 3 days of records.

Example 2: Remove the First 2 Rows — DROP

=DROP(tblSales, 2)

Use this when you want to exclude the first few rows after the header. For example, it is useful for reviewing only live data after excluding two test records.

Example 3: Extract Only the Columns You Need — CHOOSECOLS

=CHOOSECOLS(tblSales, 1, 2, 8)

This retains only the Date, Region, and Amount columns. It is very convenient for creating a simple report table and reduces the need for separate helper sheets or hidden columns.

Example 4: Stack Two Tables Vertically — VSTACK

For example, if this month’s data is in A2:H6 and next month’s data is in A10:H14:

=VSTACK(A2:H6, A10:H14)

You can combine monthly files into a single report table. If you need to consolidate many files, Power Query is more appropriate, as explained in the guide to combining multiple Excel files. However, VSTACK is much lighter for combining a few ranges on the same sheet.

Example 5: Stack Side by Side — HSTACK

=HSTACK(A2:B6, D2:E6)

This joins the left and right ranges horizontally. It is useful for quickly combining separate reference tables into a cleaner layout.

Example 6: Create a Summary Table for the Top 3 Products

=TAKE(SORT(CHOOSECOLS(tblSales, 3, 8), 2, -1), 3)

This extracts only the Product and Amount columns, sorts by Amount, and displays the top 3 rows. It is a common pattern for dashboard mini tables.

LET, LAMBDA, MAP, REDUCE, and SCAN: Shorten Formulas

These functions may seem difficult at first, but their main purpose is to reduce repeated calculations and name parts of a formula. Their benefits grow as formulas become longer.

Example 1: Name Intermediate Calculations with LET

=LET(
  onlineRows, FILTER(tblSales, tblSales[Channel]="Online"),
  sortedRows, SORT(onlineRows, 8, -1),
  TAKE(sortedRows, 3)
)

This formula produces the same result as the FILTER + SORT + TAKE combination shown earlier. However, because each step has a name, it is much easier to edit later. It is also easier for someone else who inherits the workbook to understand the structure.

Example 2: Create a Reusable Function with LAMBDA

Assume you saved the formula below in Name Manager with the name TOPNBYCHANNEL.

=LAMBDA(ch, n,
  TAKE(
    SORT(FILTER(tblSales, tblSales[Channel]=ch), 8, -1),
    n
  )
)

You can then call it in a cell like this:

=TOPNBYCHANNEL("Online", 3)

Now, when you repeat the same logic for the top 3 Online records or top 5 Retail records, you do not need to keep copying long formulas.

Example 3: Apply a Rule to Each Row with MAP

=MAP(tblSales[Amount], LAMBDA(x, IF(x>=200000, "High", "Normal")))

This quickly creates tags such as High and Normal based on amounts. You can also use a helper column, but this approach is often cleaner in array-based reports.

Example 4: The Difference Between REDUCE and SCAN

• REDUCE: Reduces multiple values to one accumulated result.
• SCAN: Shows every intermediate result in the accumulation process.

=REDUCE(0, tblSales[Amount], LAMBDA(a, v, a+v))

This returns the total of the Amount column.

=SCAN(0, tblSales[Amount], LAMBDA(a, v, a+v))

The running total for the first row, second row, third row, and so on spills in sequence. It is especially useful for cumulative sales, running totals, and progress calculations.

GROUPBY and PIVOTBY: Reduce PivotTable Clicks

Among the latest new Excel functions, GROUPBY and PIVOTBY are especially notable. Their names are straightforward: GROUPBY summarizes by one dimension, while PIVOTBY summarizes by two dimensions—rows and columns.

This does not mean PivotTables are bad tools. However, GROUPBY and PIVOTBY are more flexible when report formats change frequently or when you need to integrate a formula-based summary table into a document. For a deeper look at this topic, see the dedicated PIVOTBY and GROUPBY guide.

Example 1: Total Sales by Region — GROUPBY

=GROUPBY(tblSales[Region], tblSales[Amount], SUM)

The result appears approximately as follows.

Example 2: Region × Channel Sales Table — PIVOTBY

=PIVOTBY(tblSales[Region], tblSales[Channel], tblSales[Amount], SUM)

Like a PivotTable, this formula returns a summary table with regions in rows, channels in columns, and total amounts as values. The key benefit is that it can reduce the work of creating a monthly report as a PivotTable and then copying and formatting it again.

GROUPBY vs. SUMIFS

SUMIFS is excellent for quickly calculating a total for specific conditions when the criteria are already established. GROUPBY, on the other hand, is stronger when you need to automatically generate the criteria list and create the summary table itself. In other words, use SUMIFS when you need a single-cell result, and GROUPBY when you need an entire summary table.

Version Compatibility and Sharing Considerations

The most common issue with new Excel functions is: “It works on my computer but not on someone else’s.” This is almost always caused by version differences.

• Microsoft 365 generally receives new functions sooner.
• Excel 2024 includes several newer text and array functions, as well as LAMBDA, but availability can vary by company environment.
• If someone you share with uses an older version of Excel, functions such as XLOOKUP, FILTER, TEXTSPLIT, and GROUPBY may not work.

The Safest Way to Work

1. Create the original calculation sheet using new functions.
2. If needed, paste values into a separate sheet for sharing.
3. For collaborative workbooks, add “Microsoft 365 / Excel 2024 or later recommended” at the top of the file.
4. Do not place manually entered cells around a spill range.

What to Check When New Functions Are Missing

• Whether the signed-in account has a Microsoft 365 subscription
• Whether you are using the desktop app or Excel for the web
• Whether company security policies delay update deployment
• Whether the file is an older Compatibility Mode file (.xls) rather than a new file

Troubleshooting: Common Issues and Solutions

Workplace Checklist

• Have you converted the data to a table with Ctrl + T whenever possible?
• Have you considered XLOOKUP or INDEX+XMATCH before VLOOKUP for lookups?
• Can conditional row extraction be changed from manual filtering to FILTER?
• Can text extraction be simplified with TEXTBEFORE/TEXTAFTER/TEXTSPLIT instead of nested LEFT/MID formulas?
• Can top-N reports be automated with SORT + TAKE?
• Have you made long formulas easier to read with LET?
• Have you checked the Excel versions used by people who will receive the file?

Alternative Methods and Considerations

New Excel functions are not always the answer. For example, Power Query is more suitable than VSTACK for combining dozens of files by folder. In organizations that need to maintain reports through a click-based workflow, PivotTables may also be more familiar than PIVOTBY.

What matters is not using a new function because it looks impressive, but whether it creates a structure that is shorter, less likely to break, and reusable compared with the previous method. By that standard, most new functions are well worth learning.

Conclusion

The key to new Excel functions is not memorizing the number of functions. You will remember them much longer by connecting them to tasks: XLOOKUP for lookups, FILTER for conditional extraction, TEXTSPLIT for text splitting, TAKE for top-N results, VSTACK for combining tables, LET for organizing long formulas, and GROUPBY/PIVOTBY for formula-based summaries.

As a next step, it is useful to read the following articles in order.

• Free Guide to All Excel Functions
• Complete Guide to Multiple Conditions in the IF Function
• How to Use Excel AI Functions

If this article was helpful, bookmark it. Next, choose one actual work file and try replacing an existing VLOOKUP, helper-column, or copy-and-paste process with one or two new functions. You will notice the speed difference right away.