What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
These 10 newer Excel functions can replace many fixed-column lookups, helper columns, manual deduplication steps, and nested text formulas. They are not all brand-new: some arrived with Excel 2021, while others are marked for newer releases. Availability depends on your Excel edition and update channel, so check Microsoft’s function reference if a formula is not recognized.
The examples use English function names and comma separators; localized Excel installations may use different names or separators. Dynamic-array formulas can return results into neighboring cells, so leave room for the output.
Which functions replace older Excel approaches?
| Older approach | Newer function | Useful when |
|---|---|---|
| VLOOKUP with a fixed column number | XLOOKUP | You need a flexible lookup that can return from either side of the search range. |
| INDEX and MATCH for a flexible lookup | XLOOKUP | You want one lookup function; use XMATCH when you need a position rather than a returned value. |
| Advanced Filter or helper columns | FILTER | You need a live result containing rows that meet criteria. |
| Manual Remove Duplicates | UNIQUE | You need a distinct list that updates with the source. |
| Nested LEFT, RIGHT, FIND, or MID formulas | TEXTBEFORE, TEXTAFTER, or TEXTSPLIT | You need to extract or split text around delimiters. |
| Repeated nested calculations | LET | You want to name and reuse intermediate results within a formula. |
| Copying several ranges into one | VSTACK | You want a formula-generated vertical combination. |
| Manually assembling ranges side by side | HSTACK | You want a formula-generated horizontal combination. |
| Manually sorting formula results | SORTBY | You want a sorted view without changing the source data. |
These functions can make formulas easier to maintain, but they are not automatically faster or better in every workbook. Dynamic-array results need clear space, newer functions are not supported by every Excel edition, and older formulas may be preferable when compatibility is essential.
1. XLOOKUP: look up a value without a fixed column number
XLOOKUP searches one range and returns the corresponding value from another. Unlike VLOOKUP, the return range can be on either side of the lookup range, and the default match is exact. Microsoft documents its behavior and compatibility in the XLOOKUP reference.
Free tools Windows power users keep installed
One-click scans. No signup required.
#1 Best Overall
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
=XLOOKUP(A2,Products[Product ID],Products[Price],"Not found")This finds the value in A2 in the Product ID column and returns the matching price, or “Not found” if there is no match. Structured references make the formula easier to follow and accommodate table rows:
=XLOOKUP([@[Product ID]],Products[Product ID],Products[Price],"Missing")For a threshold lookup, explicitly request the next smaller item with -1:
=XLOOKUP(A2,TaxRates[Threshold],TaxRates[Rate],"No rate",-1)Approximate matching depends on appropriately structured lookup data; use exact matching for ordinary ID or name lookups. If keys are duplicated, XLOOKUP returns the first matching result. Excel 2016 and Excel 2019 do not support it natively, so a legacy workbook may need INDEX/MATCH or VLOOKUP instead.
2. FILTER: return only rows that meet conditions
FILTER creates a live result from rows or columns that satisfy a condition. The include range must align with the rows or columns being filtered.
=FILTER(A2:D100,D2:D100="Open","No open items")This returns rows from A:D where column D is Open. The optional third argument supplies a message if there are no matching rows. Multiply conditions for AND logic and add them for OR logic:
=FILTER(A2:D100,(B2:B100="West")*(D2:D100="Open"),"No matches")=FILTER(A2:D100,(B2:B100="West")+(B2:B100="South"),"No matches")One formula can replace a manually filtered-and-copied report or a collection of helper formulas. The result spills into adjacent cells; a non-empty cell, merged cell, or other obstruction in the output area can cause #SPILL!. Avoid unnecessarily broad ranges in large workbooks. Microsoft describes FILTER and related dynamic-array functions in its Excel function reference.
3. SORTBY: sort a result using another range
SORTBY sorts an array according to corresponding values in another range. A positive order argument sorts ascending; a negative one sorts descending.
=SORTBY(A2:D100,D2:D100,-1)This creates a descending view based on column D without reordering the source data. To sort first by column B ascending and then by column D descending:
=SORTBY(A2:D100,B2:B100,1,D2:D100,-1)For an open-items report sorted by amount, combine it with FILTER and LET. Here column D is the status and column C is the amount:
=LET(
data,FILTER(A2:D100,D2:D100="Open","No open items"),
SORTBY(data,INDEX(data,,3),-1)
)The sort-by array must have compatible dimensions with the array being sorted. Mixed text and numeric values can produce unexpected ordering, so check that the sort column is consistent. See Microsoft’s lookup and reference function reference.
4. UNIQUE: generate a live list of distinct values
UNIQUE returns distinct values from a range or array. It can replace copying a column and running Remove Duplicates when you want the result to update with the source.
=UNIQUE(B2:B100)Sort the distinct values alphabetically or numerically with:
=SORT(UNIQUE(B2:B100))To return only values that occur exactly once, use the third argument:
=UNIQUE(B2:B100,,TRUE)A sorted distinct list can also feed a data-validation drop-down; depending on workbook setup, the source may refer to a spill range such as =Lists!$A$2#. Blank cells may appear in the results. Leading or trailing spaces can make apparently identical entries distinct, so clean the source first if needed:
=SORT(UNIQUE(TRIM(B2:B100)))TRIM removes ordinary extra spaces, but imported data can contain non-breaking spaces that need separate cleaning. Microsoft’s function reference lists UNIQUE among Excel’s functions.
5. LET: name calculations inside a formula
LET assigns names to intermediate values or calculations within a formula. This can make a long formula easier to inspect and avoid repeating the same expression.
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →For example, name the status and amount ranges, then filter for open items above 1,000:
=LET(
status,D2:D100,
amount,C2:C100,
result,FILTER(A2:D100,(status="Open")*(amount>1000),"No results"),
result
)Each name is followed by its value or calculation, and the final argument is the result returned by LET. Names must follow Excel’s naming rules and must not look like cell references; avoid names such as c, which can conflict with R1C1-style references. LET improves organization, but it cannot correct faulty logic. Microsoft explains LET and its use for storing intermediate calculations in its function reference.
6. TEXTSPLIT: split delimited text into rows or columns
TEXTSPLIT separates text using column and row delimiters. To split a comma-and-space list into columns:
=TEXTSPLIT(A2,", ")To split on semicolons into rows:
=TEXTSPLIT(A2,,";")For text in A2 that reads North, West; South, East, this formula uses commas for columns and semicolons for rows:
=TEXTSPLIT(A2,", ",";")Repeated delimiters can produce blank entries; use the optional ignore_empty argument if that is not wanted. TEXTSPLIT handles simple delimiter-based text, not every rule of quoted CSV data. For importing recurring or irregular files, use a proper import workflow rather than assuming a delimiter split is a complete parser. The function’s syntax is listed in Microsoft’s text and logical function reference.
7. TEXTBEFORE: extract text before a delimiter
TEXTBEFORE returns the part of a string before a specified delimiter. For an email address in A2, this returns the username:
=TEXTBEFORE(A2,"@")For a hyphenated code, return everything before the final hyphen with an instance number of -1:
=TEXTBEFORE(A2,"-",-1)If the delimiter is absent, the formula returns an error unless you provide a fallback using its optional arguments. Check the delimiter and occurrence you intend to use, especially when it may appear more than once. TEXTBEFORE is useful for simple parsing, not for fields where delimiters can appear inside quoted data. Microsoft lists it in the Excel function reference.
Rank #4
8. TEXTAFTER: extract text after a delimiter
TEXTAFTER returns the part of a string following a specified delimiter. For an email address, it returns the domain:
=TEXTAFTER(A2,"@","No domain")For a filename with multiple periods, retrieve the extension after the final period:
=TEXTAFTER(A2,".",-1)Like TEXTBEFORE, TEXTAFTER can return an error when the delimiter is missing; a fallback helps make that case explicit. If the separator varies, use additional logic rather than assuming one delimiter is always present. Microsoft’s function reference includes TEXTAFTER among the text functions.
9. VSTACK: append ranges vertically
VSTACK appends arrays one below another into a formula result. For example, combine three monthly data ranges with matching four-column layouts:
PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minute=VSTACK(January!A2:D100,February!A2:D100,March!A2:D100)This creates a combined view; it does not merge the ranges into a maintained Excel Table. If arrays have different numbers of columns, missing positions are padded with #N/A, so normalize the layouts first. For a small, consistent set of ranges, VSTACK is convenient. For recurring consolidation from files or folders, inconsistent schemas, or transformations that need refreshing and auditing, Power Query is usually a better fit. Microsoft documents VSTACK in its Excel function reference.
10. HSTACK: place arrays side by side
HSTACK appends arrays horizontally. This example combines three columns from a worksheet:
=HSTACK(A2:A20,C2:C20,E2:E20)You can also append a lookup result to existing report data:
=HSTACK(A2:B20,XLOOKUP(A2:A20,Products[ID],Products[Price],"Missing"))Make sure the arrays align row by row: the formula can return a plausible-looking result even when the rows refer to different records. If the arrays have different row counts, the shorter result is padded with #N/A. HSTACK creates a formula result; it does not modify or extend the source table. See Microsoft’s lookup and reference functions documentation.
Recommended Free Tools
Three useful formulas to adapt
Look up a product price
=XLOOKUP(A2,Products[ID],Products[Price],"Missing")Use this when A2 contains a product ID and the Products table has ID and Price columns.
List customers with open sales, without duplicates
=SORT(UNIQUE(FILTER(Sales[Customer],Sales[Status]="Open","No open sales")))FILTER selects customers on open sales; UNIQUE removes repeats, and SORT orders the result.
Combine monthly data and sort populated rows
=LET(
data,VSTACK(January!A2:D100,February!A2:D100),
filled,FILTER(data,INDEX(data,,4)<>"","No rows"),
SORTBY(filled,INDEX(filled,,4),-1)
)This assumes the fourth column identifies populated rows and is also the sort key. INDEX here selects a column from an array; it is not one of the ten featured newer functions.
Check Excel compatibility before troubleshooting
“New” here means newer formula-era functions, not functions all released in one year. Microsoft’s reference marks functions by supported version: XLOOKUP, FILTER, SORTBY, and UNIQUE are associated with Excel 2021-era availability, while functions such as TEXTSPLIT, TEXTBEFORE, TEXTAFTER, VSTACK, and HSTACK are associated with newer releases, including Excel 2024. Microsoft 365 receives ongoing updates, and availability can vary by update channel and account. Check the category reference and alphabetical reference for the function and your version. Excel 2016 and 2019 do not natively support XLOOKUP; older perpetual versions may not calculate newer functions in a workbook.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →If a cell shows #NAME? or a formula begins with _xlfn., first check whether the Excel edition or update channel supports that function. If the workbook must work for people on older versions, use compatible alternatives such as INDEX/MATCH or VLOOKUP and test the workbook in the target environment.
Understand spill behavior
FILTER, UNIQUE, TEXTSPLIT, VSTACK, and HSTACK can return multiple cells from one formula. If the output area is blocked, Excel may show #SPILL!. To diagnose it:
- Select the formula cell and inspect Excel’s spill-range warning.
- Clear the cells blocking the intended result.
- Unmerge cells that overlap the spill area.
- Check that criteria and sort arrays align with the rows or columns in the source.
- Confirm the source range does not include unexpected data that makes the result larger than intended.
To refer to an entire spilled result, use the spill operator #. If a formula in G2 is =SORT(UNIQUE(B2:B100)), another formula can refer to its current output with =G2#.
Check data quality and array alignment
Modern functions do not repair inconsistent source data. Look for extra spaces, non-breaking spaces, numbers stored as text, inconsistent capitalization, duplicate IDs, blank rows, inconsistent delimiters, and dates stored in incompatible formats. Supporting functions such as TRIM, CLEAN, VALUE, SUBSTITUTE, and IFERROR may help, but choose cleaning logic that matches the data. A mismatched array size can cause an error; a mismatched row order can produce a valid-looking but incorrect report.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Clear out junk files and repair common Windows errors3Scan for outdated or missing drivers - takes under a minuteWhen a formula is not the right tool
Use an Excel Table with structured references when a dataset grows as rows are added and formulas should remain readable. Use Power Query for repeatable imports and transformations, especially when consolidating files or handling inconsistent source schemas. Use INDEX/MATCH or VLOOKUP when compatibility with older Excel versions is a requirement. A one-cell legacy formula can also be easier to hand off than a spilled result when the receiving workbook or application does not support dynamic arrays.
For reusable custom workbook functions, Microsoft’s LAMBDA documentation explains how to define a function without VBA, macros, or JavaScript; it is supported in Microsoft 365, Excel 2024, and Excel 2021 according to Microsoft. This is an additional option, not a prerequisite for using the ten functions above.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
