Old Excel functions you should stop using (and what to replace them with)

Excel is packed with functions, but if you’re still relying on VLOOKUP, nested IF statements, or CONCATENATE, you’re making your spreadsheets harder to manage than they need to be. Microsoft has newer, more powerful Excel functions that make you feel like a spreadsheet wizard and make your formulas easier to read.

The good news is you don’t need to be an Excel expert—they’re designed to replace the clunky, outdated ones you’re probably already familiar with. Whether you’re looking up data, building dynamic lists, or working with text, there are better tools built right into Excel that can save you time.

5

Replace the restrictive VLOOKUP

XLOOKUP is far more flexible

VLOOKUP has been the go-to function for looking up data in Excel for years, but it comes with limitations. You can only search in the leftmost column and return values to the right. If your lookup column isn’t on the far left, you’re stuck rearranging your entire table or adding helper columns.

XLOOKUP fixes all of that. It can search anywhere in your table and return values from any column—whether it’s to the left, right, or anywhere else. Plus, the syntax is simpler and easier to remember.

Here’s how the syntax compares:

VLOOKUP:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])​​

• lookup_value: The value you’re searching for.
• table_array: The range containing your data.
• col_index_num: The column number to return (counting from the left).
• range_lookup: TRUE for an approximate match, FALSE for an exact match.

XLOOKUP:

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

• lookup_value: The value you’re searching for.
• lookup_array: The column where you want to search.
• return_array: The column to return the result from.

You can also use the following optional parameters:

• if_not_found: Custom message if no match is found.
• match_mode: Exact match (0), or other match types.
• search_mode: Search from first to last, or reverse.

Let’s say you have a sales table with product IDs in column A, product names in column B, and prices in column C. If you want to look up the price for product ID “PR-12-Y,” VLOOKUP would require you to count that the price is in the third column:

=VLOOKUP("PR-12-Y", A2:C100, 3, FALSE)

With XLOOKUP, you just specify which columns to search for and return:

=XLOOKUP("PR-12-Y", A2:A100, C2:C100)

If you add a new column between Product Name and Price later, the VLOOKUP formula breaks because the column index is now wrong. XLOOKUP keeps working because it references the actual column ranges, not a number. You can also use XLOOKUP to search in column C and return values from column A—something VLOOKUP can’t do.

XLOOKUP is only available in Microsoft 365 and Excel 2021 or later. If you’re using an older version, you’ll need to stick with VLOOKUP or upgrade your Excel subscription.

4

Ditch messy nested IF statements

IFS function are much cleaner

Nested IF statements get out of hand fast. When you’re testing multiple conditions, you end up with a formula that’s full of parentheses and nearly impossible to read or troubleshoot. Miss one closing parenthesis, and the whole thing breaks.

The IFS function solves this by letting you test multiple conditions in a single, straightforward formula. So, no more nesting is required; just list your conditions and corresponding results in order.

Here’s how the syntax compares:

Nested IF:

=IF(condition1, value1, IF(condition2, value2, IF(condition3, value3, value4)))

IFS:

=IFS(condition1, value1, condition2, value2, condition3, value3, TRUE, default_value)

• condition1, condition2, condition3: Logical tests to evaluate.
• value1, value2, value3: Results to return if each condition is TRUE.
• TRUE, default_value: Optional catch-all for when no conditions are met.

Let’s say you’re working with sales data and want to assign performance ratings based on total sales. With nested IF statements, the formula would look like this:

=IF(G2>=5000, "Excellent", IF(G2>=4000, "Good", IF(G2>=3000, "Average", "Below Average")))

That’s hard to read, and if you need to add another condition or adjust a threshold, you’re hunting through parentheses. With IFS, the same logic is much cleaner:

=IFS(G2>=5000, "Excellent", G2>=4000, "Good", G2>=3000, "Average", TRUE, "Below Average")

Each condition is clear, and you can easily spot the thresholds and ratings. If you need to change “Good” to start at 4,500 instead of 4,000, you just update that one line. The TRUE at the end acts as a fallback for anything that doesn’t meet the earlier conditions—basically replacing the final “else” in a nested IF.

3

Stop using the tedious CONCATENATE

Switch to the powerful TEXTJOIN function

CONCATENATE forces you to reference every single cell individually and manually type separators, such as commas or spaces, between each one. If you’re combining five cells, that’s five cell references plus four separators—all typed out separately.

TEXTJOIN handles this in one go. You specify the delimiter once, tell it whether to ignore empty cells, and then select the entire range. It’s faster to write and much easier to edit later.

Here’s the syntax comparison:

CONCATENATE:

        =CONCATENATE(text1, " ", text2, " ", text3)
    

TEXTJOIN:

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

• delimiter: The character or text to insert between each value (comma, space, hyphen, etc.).
• ignore_empty: TRUE to skip blank cells, FALSE to include them.
• text1, [text2], …: The cells or ranges you want to combine.

Let’s say you have a list of products in columns A through D and want to create a single string that lists all four items separated by commas. With CONCATENATE, you’d write:

        =CONCATENATE(A2, ", ", B2, ", ", C2, ", ", D2)
    

That works, but if any cell is empty, you still get the comma and space. With TEXTJOIN, you can handle the same task like this:

        =TEXTJOIN(", ", TRUE, A2:D2)
    

The TRUE argument tells Excel to skip empty cells so that you won’t end up with extra commas or spaces in your result. If you’re combining product names, sales regions, or any data where some cells might be blank, this saves you from messy output.

TEXTJOIN also works across larger ranges. If you need to combine 20 cells, you don’t have to type out 20 references—just specify the range once.

2

Forget manual list building

Use dynamic arrays like FILTER and UNIQUE instead

Building filtered lists manually means copying rows one by one or using AutoFilter, then copying and pasting the results to another location. If your source data changes, you have to repeat the whole process. It’s time-consuming and prone to errors.

Dynamic array functions such as FILTER and UNIQUE generate lists automatically and update in real time when your source data changes. You write the formula once, and Excel handles the rest. I use Excel’s FILTER function for everything now, as I don’t need to manually refresh or copy and paste.

Here’s the syntax for both:

FILTER:

=FILTER(array, include, [if_empty])

• array: The range of data you want to filter.
• include: The condition that determines which rows to include.
• if_empty: Optional message to display if no results match the condition.

UNIQUE:

=UNIQUE(array, [by_col], [exactly_once])

• array: The range you want to extract unique values from.
• by_col: FALSE to compare rows (default), TRUE to compare columns.
• exactly_once: FALSE returns all unique values, TRUE returns values that appear only once.

Let’s say you have a sales table with Product IDs, Product Names, Regions, and Sales figures. If you want to see only sales from the “East” region, you can use FILTER:

=FILTER(B2:H33, B2:B33="East", "No results found")

This pulls all rows where the region in column B equals “East” and displays them. If someone adds a new “East” region sale to the source data, it appears in your filtered list automatically. The “No results found” message shows up if no rows match your condition.

For UNIQUE, if you want a clean list of all product categories without duplicates, you’d write:

=UNIQUE(C2:C33)

This scans column C and returns each unique product category once. If the same product appears 20 times in your source data, it appears only once in the results. You can also combine both functions—filter your data first, then extract unique values from the filtered results.

Dynamic array functions require Microsoft 365 or Excel 2021. If your version doesn’t support them, the formulas will return #NAME?, one of the common Excel errors.

1

Swap LEFT, RIGHT, and MID

Because TEXTSPLIT, TEXTBEFORE, and TEXTAFTER are more intuitive

LEFT, RIGHT, and MID require you to count exactly how many characters you want to extract. If your text format changes or its length varies, you’re constantly adjusting character counts, and a single miscalculation breaks the formula.

The newer text functions—TEXTSPLIT, TEXTBEFORE, and TEXTAFTER—let you extract text based on delimiters or specific markers instead of character positions. They’re among the Excel functions I use most because of their flexibility and ease of understanding at a glance.

Here’s the syntax for each:

TEXTSPLIT:

=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])

• text: The text string you want to split.
• col_delimiter: Character that separates columns (comma, space, hyphen, etc.).
• row_delimiter: Optional character that separates rows.
• ignore_empty: TRUE to skip empty values, FALSE to include them.
• match_mode: 0 for case-sensitive, 1 for case-insensitive.
• pad_with: Value to use for empty cells in the result.

TEXTBEFORE:

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

• text: The text string to search.
• delimiter: Character or text that marks where to stop extracting.
• instance_num: Which occurrence of the delimiter to use (1 for first, 2 for second, etc.).
• match_mode: 0 for case-sensitive, 1 for case-insensitive.
• match_end: 0 to search from the start, 1 to search from the end.
• if_not_found: Value to return if the delimiter isn’t found.

TEXTAFTER:

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

Parameters are the same as TEXTBEFORE, but it extracts text after the delimiter instead of before.

Let’s say you have product codes formatted as “PRD-12345-X” and want to extract just the middle part. With MID, you’d need to count characters:

=MID(A2, 5, 5)

That only works if “12345” is always five characters. With TEXTBEFORE and TEXTAFTER, you can extract it based on the hyphens:

=TEXTAFTER(TEXTBEFORE(A2, "-", 2), "-")

It grabs everything before the second hyphen, then everything after the first hyphen in that result. If your product code changes to “PRD-12345-X,” it still works without requiring any adjustments to character counts.

For TEXTSPLIT, if you have full names in “First Last” format and want to separate them into two columns, you’d write:

=TEXTSPLIT(E2, " ")

Excel automatically splits the name at the space and puts the first name in one column and the last name in the next. With LEFT and RIGHT, you’d need separate formulas and have to use the FIND function to locate the space first—much more complicated.

Start with one function replacement and build from there

The old functions still work, so Excel won’t force you to switch. But if you’re spending extra time fixing broken formulas or counting characters manually, the newer alternatives are worth trying.

Pick one function from this list that matches a task you do regularly—maybe you’re constantly using VLOOKUP or building nested IFs. Replace it in one spreadsheet and see how it works. Once you get comfortable with the syntax, the time savings add up quickly.