Outdated Excel Functions (and What to Use Instead)

Excel is always evolving, and so should your formulas. While classic functions like VLOOKUP, MATCH, and OFFSET were once essential, today we have better, more reliable, and more efficient options.

This post walks you through modern replacements that simplify your work and future-proof your spreadsheets.

I've also included downloadable examples and reference tables to help you make the switch confidently.

Watch the Outdated Excel Functions Video

 

Get the Excel Example File

Outdated Excel Functions – Step-by-step

If you prefer a written tutorial on Outdated Excel Functions with examples and comparison tables, continue reading.

1. VLOOKUP / HLOOKUP → XLOOKUP

The VLOOKUP function has long been a go-to lookup tool, but it has serious limitations — it only looks right, it breaks when you insert columns, and it returns approximate matches unless you specify otherwise.

Better Alternative: XLOOKUP

Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Feature	XLOOKUP	VLOOKUP / HLOOKUP
Search direction	Any direction (left, right, up, down)	Top to bottom (VLOOKUP) or left to right (HLOOKUP) only
Lookup column/row position	Lookup can be in any column or row	Must be first column (VLOOKUP) or first row (HLOOKUP)
Exact match by default	✅ Yes (default is exact match)	❌ No (default is approximate which is error prone)
Approximate match	✅ Yes (optional)	✅Yes (default)
Return multiple columns/rows	✅ Yes (can return multiple columns/rows easily)	❌ No (one value only)
Insert/delete column or row issues	✅Doesn't break because lookup and return arrays are separate	❌ Breaks if columns/rows are inserted or deleted
Error handling (custom messages)	✅Built-in with the optional [if_not_found] argument	❌ Need to wrap with IFERROR manually
Support for dynamic arrays (spilled arrays)	✅Yes	❌ No
Search from first or last match	✅ Yes (search_mode option)	❌ No

Taking the data below, you can see the comparative formulas on rows 18 and 19:

Check out the deep dive XLOOKUP function tutorial.

2. CONCATENATE / CONCAT → TEXTJOIN

Combining text from multiple cells used to require clunky formulas — and blank cells would result in unwanted double spaces or missing separators.

Better Alternative: TEXTJOIN

Syntax: =TEXTJOIN(delimiter, ignore_empty, text1,...)

Feature	TEXTJOIN	CONCAT	CONCATENATE
Skip blanks	✅ Yes (ignore_empty)	❌ No	❌ No
Separator support	✅ Built-in	❌ No	❌ No
Combine ranges directly	✅ Yes	✅ Yes	❌ No (must list each cell)
Supports array formulas	✅ Yes	✅ Yes	❌ Limited
Accepts ranges	✅ Yes	✅ Yes	❌ No
Available in Excel 365/2019+	✅ Yes	✅ Yes	⚠️ Deprecated

Example: column E below shows the limitation of the CONCAT function when handling empty cells, with a double space between ‘Emily Johnson’ in cell E11, whereas TEXTJOIN in cell F11 handles it with ease:

TEXTJOIN can also create lists in a single column and skip any blanks:

For more on the TEXTJOIN function and workarounds for earlier versions of Excel, check out the TEXTJOIN tutorial here.

3. MATCH → XMATCH

MATCH helps find the position of a value in a list - often used with INDEX. But it’s limited in direction and requires manual match type setup.

Better Alternative: XMATCH

Syntax: =XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

Feature	XMATCH	MATCH
Search direction	Top-to-bottom or bottom-to-top	Only first match (top to bottom)
Exact match	✅ Yes (default)	✅ Yes
Approximate match	✅ Yes (simpler options)	✅ Yes (needs setting 1 or -1)
Wildcards support (e.g., * or ?)	✅ Yes	✅ Yes
Find next larger or smaller item	✅ Yes (easier options)	✅ Yes
Return position from bottom	✅ Yes (search_mode)	❌ No
Supports Regex Match	✅ Yes (native support)	❌ No

Example: In the example below, rows 14 and 15 show equivalent formulas. XMATCH doesn’t need the additional match mode argument because an exact match is the default. In row 16 you can see the search from bottom capabilities of XMATCH.

Step 4: Add Notes and Bank Details

4. SUBTOTAL → AGGREGATE

SUBTOTAL is great for calculating only visible rows, but it falls short when errors are present in the data and has limited aggregation options.

Better Alternative: AGGREGATE

Syntax: =AGGREGATE(function_num, options ref1,...)

Feature	AGGREGATE	SUBTOTAL
Basic aggregations (SUM, AVERAGE, COUNT, etc.)	✅ Yes	✅ Yes
Option to ignore nested SUBTOTAL/AGGREGATE results	✅ Yes	✅ Yes
Option to ignore hidden rows	✅ Yes (with options)	✅ Yes (function numbers 1–11)
Option to ignore errors	✅ Yes	❌ No
Supports more functions (e.g., LARGE, SMALL, MEDIAN)	✅ Yes (19 total functions)	❌ No (only 11 functions)
Built-in error handling	✅ Yes (ignore #DIV/0!, etc.)	❌ No

Examples: In the examples below, we can see that while SUBTOTAL fails when there are errors in the range (row 14), AGGREGATE handles it with ease (row 15). Additionally, AGGREGATE can perform tasks like returning the second largest visible sales value (row 16):

For more innovative solutions, check out the deep dive tutorial on the AGGREGATE function.

5. LEFT, MID, FIND, LEN → TEXTAFTER, TEXTBEFORE

Extracting parts of text used to require multi-layered formulas that were difficult to build and troubleshoot.

Better Alternative: TEXTAFTER, TEXTBEFORE

Syntax:

=TEXTBEFORE(text,delimiter, [instance_num], [match_mode], [match_end], [if_not_found])

=TEXTAFTER (text,delimiter, [instance_num], [match_mode], [match_end], [if_not_found])

Feature	TEXTAFTER / TEXTBEFORE	LEFT + MID + FIND + LEN
Syntax complexity	Low (simple and readable)	High (need nested formulas)
Handles first occurrence easily	✅ Built-in	⚠️ Only with careful FIND setup
Handles second, third, nth occurrence	✅ Simple (with instance_num argument)	❌ Hard (needs complex nesting)
Easy to extract before/after a delimiter	✅ Yes (direct)	❌ No (manual calculation needed)
Dynamic delimiter support	✅ Built-in	⚠️ Possible but messy
Error handling for missing delimiter	✅ Optional fallback value	❌ No (Requires IFERROR)

Examples:

There’s also a sibling function called TEXTSPLIT that enables you to split by multiple delimiters across columns or rows. Check out the deep dive TEXTBEFORE, TEXTAFTER and TEXTSPLIT tutorial here.

6. INDIRECT → SWITCH or XLOOKUP

INDIRECT was a clever trick to create dynamic references, but it’s volatile and fragile.

Better Alternatives: SWITCH or XLOOKUP

Syntax: =SWITCH(expression, value1, result1, [default_or_value2], [result2],...)

Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Old Use of INDIRECT	Why People Used It	Better Modern Solution
Dynamic named ranges (e.g., changing the range based on input)	Let users select a range to use (e.g., "Q1Sales", "Q2Sales")	✅ CHOOSE, XLOOKUP, or SWITCH can now map names directly without needing volatile references
Selecting columns dynamically	Build a reference like A:A or B:B based on a cell	✅ Use INDEX(array, , column_number) instead - much faster and non-volatile
Building dynamic ranges for SUMIFS etc.	Create flexible ranges without manual editing	✅ Use dynamic arrays (e.g., FILTER, SEQUENCE, INDEX), or structured tables with better formulas
Referencing spilled array results indirectly	Point formulas at spill ranges	✅ Use # spill references directly now, e.g., =A2#
Making validation lists dynamic	Build dropdown lists that grow automatically	✅ Use dynamic arrays with UNIQUE, SORT, or Excel Tables (structured references)
Pulling data from different tables dynamically	Choose which table or range to pull from	✅ Use CHOOSE, SWITCH, or structured tables
Dynamic sheet references (e.g., build a reference like 'Sheet2'!A1)	Needed to reference different sheets based on a cell value	❌ No true alternative yet for dynamic sheet names - still need INDIRECT (or VBA / LAMBDA hacks)

Examples: Return a dynamic range based on the drop-down list in cell G6 that toggles between the different table (TblSales) columns (C & D):

Check out the deep dive tutorials:

• XLOOKUP function tutorial
• SWITCH function tutorial

7. OFFSET → Trim Ref Dot Operator or Excel Tables

OFFSET helped create dynamic ranges, but it’s another volatile function that can slow your workbook.

Better Alternatives: Trim Ref Dot Operator or Excel Tables

Feature	OFFSET	Dot Ref	Excel Tables
Volatile	✅ Yes	❌ No	❌ No
Dynamic range support	✅ Yes	✅ Yes	✅ Yes
Spill compatibility	❌ No	✅ Yes	✅ Yes
Auto-expanding formulas	❌ No	✅ Yes	✅ Yes

Trim Ref Dot Operator Example: Column E below contains a dynamic range using OFFSET, compared to column F which uses the trim ref dot operator which is simply a dot after the colon in the first argument C10:.C30:

Excel Table Structured Reference Example: In column E, the Category column, the formula uses the table structured references. These automatically include any new data added to the table, without the need for complex formulas like OFFSET:

Note: Dot references work well for formulas that spill arrays; Tables are ideal for structured data and auto-filling.

Check out the deep dive tutorials:

• Excel Tables and Structured References
• TRIMRANGE and the Trim Ref Dot Operator

Want to Go Deeper?

If you'd like help writing cleaner formulas and mastering all the new Excel tools, check out my Advanced Excel Formulas Course. I’ll walk you through step-by-step lessons with examples you’ll actually use at work.