INDEX MATCH vs XLOOKUP: Why I Still Use INDEX MATCH

XLOOKUP is elegant, modern, and convenient. But for serious financial modelling work, INDEX MATCH still wins.

Every few months, someone tells me I should switch to XLOOKUP. It’s cleaner, they say. Easier to read. Microsoft built it specifically to replace VLOOKUP. They’re not wrong about any of that. XLOOKUP is a genuinely good function. If you’re building a quick analysis, a one-off report, or a lookup in a personal spreadsheet, XLOOKUP is fine. Use it. Enjoy it.

But if you’re building a financial model — something that will be reviewed, audited, handed to a client, used for a nine-figure investment decision, or maintained by someone who isn’t you — INDEX MATCH is still the better tool. Here’s why.

1. Cell References Tell You What’s Happening

This is the most important argument, and the one that XLOOKUP advocates tend to gloss over. When you write an INDEX MATCH formula, the cell references in the formula directly correspond to the ranges involved:

=INDEX(DataSheet!F$10:F$200, MATCH(A15, DataSheet!A$10:A$200, 0))

You can see exactly which column the result comes from (F), exactly which column the lookup key lives in (A), and exactly which range is being searched. When you audit the formula — pressing F2 and watching the coloured range indicators — each reference highlights on the sheet, and you can visually confirm that the formula is doing what it should.

XLOOKUP hides this:

=XLOOKUP(A15, DataSheet!A$10:A$200, DataSheet!F$10:F$200)

The return range is the third argument, not the first. The lookup array is the second argument. If you’re reading the formula cold, you have to mentally parse the argument order every time. In a model with hundreds of lookups, that mental overhead adds up. In a financial model, auditability isn’t a nice-to-have — it’s a requirement. INDEX MATCH wins on auditability.

2. Two-Dimensional Lookups Without Workarounds

This is where INDEX MATCH pulls decisively ahead.

A two-dimensional lookup — finding a value at the intersection of a row match and a column match — is trivial with INDEX MATCH:

=INDEX(DataRange, MATCH(RowKey, RowHeaders, 0), MATCH(ColKey, ColHeaders, 0))

One formula. Clean. Auditable. The row match and column match are both visible. XLOOKUP can do this, but it requires nesting an XLOOKUP inside another XLOOKUP, returning an entire column from the inner function. It works, but it’s ugly and unintuitive:

=XLOOKUP(RowKey, RowHeaders, XLOOKUP(ColKey, ColHeaders, DataRange))

In financial models, two-dimensional lookups appear constantly — pulling a value for a specific line item in a specific period from a summary table, for example. INDEX MATCH handles this natively and transparently. XLOOKUP requires a workaround.

3. MATCH Is a Standalone Powerhouse

When you use INDEX MATCH, you often store the MATCH result separately — calculating the row position once and referencing it in multiple INDEX formulas. This is enormously powerful in financial models where you might need to pull 20 different columns from the same matched row.

Row position: =MATCH(A15, DataSheet!A$10:A$200, 0)
Revenue: =INDEX(DataSheet!B$10:B$200, $Z15)
Cost of sales: =INDEX(DataSheet!C$10:C$200, $Z15)
Gross profit: =INDEX(DataSheet!D$10:D$200, $Z15)

The MATCH runs once. Twenty INDEX calls reference that single result. It’s faster, DRYer, and if the lookup key changes, you update one cell.

XLOOKUP doesn’t decompose like this. Each XLOOKUP is a self-contained operation. If you need 20 values from the same matched row, you write 20 XLOOKUPs, each performing its own search. That’s 20 times the computation and 20 places where the lookup key could be inconsistent.

4. Backward Compatibility Matters

XLOOKUP was introduced in Microsoft 365 and Excel 2021. It does not exist in Excel 2019, Excel 2016, or any earlier version.

“Nobody uses Excel 2016 anymore” is something said exclusively by people who have never worked with a large corporate client, a government department, or a private equity fund’s portfolio company running legacy IT. I review models that need to work on Excel 2016 regularly. Some clients are on even older versions.

A model built with XLOOKUP that gets sent to a client on Excel 2019 shows #NAME? errors in every lookup cell. That’s not a theoretical risk — it’s happened, and it will keep happening for years.

INDEX and MATCH have existed since Excel 4.0, released in 1992. They work everywhere. They always have. They always will.

5. Performance at Scale

In very large models — tens of thousands of rows, hundreds of lookup operations — the performance characteristics matter.

INDEX MATCH with a pre-calculated MATCH column (as described in point 3) means the expensive search operation happens once per lookup key. The INDEX calls are essentially O(1) — they’re just reading a cell from a known position.

XLOOKUP performs a full search for every call. In a model with 50 columns being pulled from the same source, that’s 50 searches instead of 1 search plus 50 instant lookups. On large datasets, this difference is measurable.

For a small model, it doesn’t matter. For a model with 200,000 rows and circular iteration, it can be the difference between a tolerable recalculation time and an intolerable one.

6. The “Look Left” Myth

The biggest selling point of XLOOKUP over VLOOKUP is that it can look left — the return column doesn’t have to be to the right of the lookup column.

But INDEX MATCH has always been able to look left. It has always been able to look in any direction, any column, any row. This has never been a limitation.

XLOOKUP solved VLOOKUP’s problem, not INDEX MATCH’s problem. If the main argument for XLOOKUP is that it beats VLOOKUP, that’s not an argument for switching from INDEX MATCH — it’s an argument for people still using VLOOKUP to upgrade. To INDEX MATCH, ideally.

7. Error Handling

XLOOKUP has a built-in if_not_found argument, which is convenient:

=XLOOKUP(A15, LookupRange, ReturnRange, "Not found")

INDEX MATCH requires an IFERROR wrapper:

=IFERROR(INDEX(ReturnRange, MATCH(A15, LookupRange, 0)), "Not found")

I’ll concede this is cleaner in XLOOKUP. But in a financial model, I would argue that silently handling lookup errors with a default value is dangerous. If a lookup fails, I want to know about it. I’d rather see a #N/A that forces me to investigate than a quiet “Not found” or zero that slips through review.

In practice, I use error checks and validation formulas rather than inline error handling. The IFERROR “disadvantage” is actually a safety feature.

When XLOOKUP Is Fine

I’m not a zealot. XLOOKUP is perfectly good for:

• Quick, one-off analyses that won’t be shared or audited
• Dashboards and reports where simplicity of reading matters
• Situations where you’re certain the audience is on Microsoft 365
• Simple, single-column lookups where you’d otherwise use VLOOKUP

If you’re not building a financial model — if you’re doing ad hoc analysis, reporting, or data processing — use whatever you’re fastest with. XLOOKUP is fine.

The Bottom Line

Financial modelling is about rigour, auditability, and minimising the places where errors can creep in. INDEX MATCH gives you transparent cell references, native two-dimensional lookups, the ability to decompose and reuse MATCH results, universal backward compatibility, and better performance at scale.

XLOOKUP is a convenience function that solves VLOOKUP’s problems. INDEX MATCH is a power tool that solves financial modelling’s problems. They’re not the same thing. Some things never change. INDEX MATCH is one of them. And it shouldn’t.

Will Wardle is the founder of Financial Modelling Consultants. He has been building financial models for 25 years, starting at a Big 4 accountancy firm where he developed the firm’s internal modelling add-in and trained 30 modellers. He still uses INDEX MATCH.