XLOOKUP and Dynamic Arrays: What You Unlock When You Migrate from .xls to .xlsx

Most of what people worry about when migrating from .xls to .xlsx is what might break — formulas that calculate differently, macros that stop running, formatting that shifts. Those concerns are real and worth addressing. But there's an upside that doesn't get talked about nearly enough: the features you gain access to once you're out of the .xls format.

The .xls format (BIFF8) was frozen in 2003. Every modern Excel feature added since then — XLOOKUP, dynamic arrays, the FILTER function, Power Query integration, Copilot — requires .xlsx or newer. If you've been living in .xls files, you've been cut off from more than two decades of spreadsheet innovation. Our complete guide to legacy Excel migration covers how to make the move safely.

Here's what becomes available when you migrate.

XLOOKUP: the lookup function VLOOKUP should have been

XLOOKUP was introduced in Excel 365 in 2019 and requires a modern Excel version (365 or 2021+). While technically usable in .xls files opened in modern Excel, compatibility mode limits spill behavior and the formulas won't survive if saved back to .xls. If you've been using VLOOKUP in .xls files for years, migrating is a natural opportunity to modernize your lookup formulas — though it's not required. Your existing VLOOKUPs will continue to work identically in .xlsx.

Why XLOOKUP is worth learning after your migration:

• No column index number. VLOOKUP requires you to specify the column by position (the dreaded third argument that breaks when you insert a column). XLOOKUP takes the return range directly.
• Looks left by default. VLOOKUP can only look to the right of the lookup column. XLOOKUP can look in any direction — the lookup column doesn't need to be the leftmost.
• Better error handling. XLOOKUP has a built-in if_not_found argument. No need to wrap in IFERROR.
• Exact match by default. VLOOKUP defaults to approximate match (which bites everyone at least once). XLOOKUP defaults to exact match.

Replacing a VLOOKUP with XLOOKUP:

You don't need to rewrite your existing VLOOKUPs after migration — they work fine. But for new formulas, or when you're editing existing ones, XLOOKUP is the better tool.

Dynamic arrays: formulas that return multiple results

This is the bigger architectural change, and it only works in .xlsx. Dynamic array formulas can return a range of results that "spills" into multiple cells automatically — no Ctrl+Shift+Enter, no array locking, no pre-sizing output ranges.

FILTER — the function that replaces 80% of helper columns

FILTER returns a subset of a table based on one or more conditions. In .xls, you'd need AutoFilter, a helper column with IF formulas, or a complex array formula. In .xlsx:

This returns all rows where column C equals "Active." If you add a row to the source data, the filter result updates automatically. No manual refresh, no dragging formulas down.

SORT and SORTBY — sorted results without rearranging data

SORT returns a sorted version of a range without moving the source data. SORTBY sorts by a different column than the one being returned.

Useful for dashboards where you want a sorted view of data that's stored in entry order.

UNIQUE — deduplicate without removing source data

UNIQUE returns a list of unique values from a range. In .xls you'd need Remove Duplicates (destructive) or a pivot table. In .xlsx:

The result updates as the source data changes. Combine with SORT for a clean, auto-updating unique list:

SEQUENCE — generate number series without filling down

SEQUENCE generates a sequential array. Instead of typing 1, 2, 3... and filling down:

What about your existing CSE array formulas?

If you've been using Ctrl+Shift+Enter (CSE) array formulas in .xls — the curly-brace {=SUM(IF(...))} style — those work fine in .xlsx and don't need to be rewritten. They continue to operate as legacy array formulas.

The difference is that in .xlsx, you no longer need CSE for new formulas that return arrays. =SUM(IF(A1:A10>5, B1:B10, 0)) entered as a regular formula (no Ctrl+Shift+Enter) will work correctly in .xlsx because dynamic arrays handle the array evaluation automatically.

Microsoft calls this the "dynamic array engine" and it applies to all formulas in .xlsx, not just the new functions. It's a significant change to how Excel evaluates formulas internally — one that's completely invisible until you try to use it.

Microsoft 365 Copilot requires .xlsx

If your organization has Microsoft 365 Copilot enabled, it won't analyze or generate content in .xls files — only .xlsx. This is increasingly relevant as IT teams roll out Copilot to business users. Files still in .xls format will be invisible to Copilot's capabilities.

For knowledge workers who rely on Excel for analysis, being stuck in .xls means being cut out of AI-assisted analysis, formula suggestions, and data summarization — even if Copilot is licensed and available.

Power Query integration

Power Query (Get & Transform) is technically available in .xls files in modern Excel, but with significant limitations. Queries saved in .xls files don't always persist correctly, and some transformation steps behave differently. In .xlsx, Power Query is a first-class feature: queries save reliably, refresh on demand, and integrate cleanly with Tables.

If you're doing any data import, transformation, or consolidation work in Excel, .xlsx with Power Query is dramatically more capable than anything available in .xls.

Excel Tables work better in .xlsx

Structured Tables (Insert → Table, the ListObject feature) exist in both formats, but in .xls they have row limits (65,536 rows vs .xlsx's 1,048,576) and some features behave inconsistently. In .xlsx, Tables are the recommended way to structure data because they integrate with dynamic array formulas, Power Query, and PivotTables more cleanly.

If your .xls files use named ranges to give data structure, migrating to .xlsx and converting those ranges to Tables unlocks structured references like =Sales[Amount] instead of =$D$2:$D$500.

What doesn't change

The migration doesn't change your existing formulas. VLOOKUP, SUMIF, COUNTIF, INDEX/MATCH, nested IF statements — all of it works identically in .xlsx. The format change unlocks new capabilities without removing old ones.

VBA macros also survive the migration in .xlsm format (the macro-enabled .xlsx variant). Your existing automation continues to work — the migration just removes the format constraint that's been limiting what else you can use alongside it.

The formula modernization opportunity

A .xls to .xlsx migration is a natural point to audit your spreadsheets and ask: which of these formulas could be cleaner with modern functions? You don't have to rewrite anything — but the migration hands you a moment to step back and look at workbooks that may have been in use since Excel 2003, unchanged, while the tool evolved around them.

The VLOOKUP that was the right answer in 2008 is still correct in 2026 — but XLOOKUP is less brittle. The CSE array formula that calculated the right answer is still valid — but FILTER is simpler to read and maintain. Migrating the format is the prerequisite. What you do with the new capabilities afterward is up to you.

← Back to all posts