What changed when Excel got dynamic arrays
Excel 365 (and Excel 2021+) added a small set of new functions that quietly obsolete a lot of older patterns. The legacy versions still work — but the modern ones are clearer, shorter, and harder to break. Here's what to swap, and what to keep.
VLOOKUP → XLOOKUP
The most common upgrade. XLOOKUP doesn't care about column order, has a built-in not-found case, and survives column insertions.
=IFERROR( VLOOKUP(A2, table, 4, FALSE), "Not found" )
Breaks if you insert a column before column 4. Two functions to express one idea.
=XLOOKUP(A2, names, emails, "Not found")
References columns by range, not position. Built-in fallback. Reads like the goal.
Nested IF → IFS
Once your IFs go three levels deep, switch.
=IF(A2>=90,"A",
IF(A2>=75,"B",
IF(A2>=60,"C","D")))
Match the parens. One missing close paren breaks everything.
=IFS( A2>=90,"A", A2>=75,"B", A2>=60,"C", TRUE,"D" )
Reads top-down, no nesting, ends with a clear default.
CONCATENATE → TEXTJOIN
CONCATENATE can't take a range. TEXTJOIN can — and it skips blanks for you.
=CONCATENATE(A2,", ",A3,", ",A4,", ",A5)
Hard-coded list. Empty cells leave double commas.
=TEXTJOIN(", ", TRUE, A2:A20)
Range argument, automatic empty-cell skip.
Drag-to-fill → Spilled formulas
Stop dragging formulas down 5,000 rows.
// In B2, drag down to B5000: =A2 * 1.18
Drag must be redone whenever the source grows.
=A2:A5000 * 1.18
Single formula, one cell, spills automatically. Or use a Table reference for self-extending ranges.
LEFT / MID / FIND → TEXTSPLIT
Splitting "Last, First" used to take three nested calls.
// First name:
=TRIM(MID(A2, FIND(",", A2)+1, 99))
// Last name:
=LEFT(A2, FIND(",", A2)-1)
=TEXTSPLIT(A2, ", ")
Spills into two cells. Add a third delimiter for splitting addresses or CSVs.
When the legacy version is still right
- You're sharing the file with users on Excel 2019 or older — they don't have XLOOKUP, FILTER, LET, or LAMBDA.
- The file has to open in older Excel for Mac or Excel Online without 365 — many dynamic-array features fall back to
_xlfn.errors. - Performance matters and you only need a single value — VLOOKUP can be marginally faster than XLOOKUP on huge datasets when used correctly.
- You're maintaining a workbook full of helper cells that other formulas depend on — converting to a single spilled array can break those references.