XLOOKUP vs VLOOKUP vs INDEX/MATCH: which lookup should you use?

Three formulas do the same core job — pull a value out of a table by its key — and finance people argue about them like sports teams. Here's the short version, then the honest, side-by-side version.

VLOOKUP: the one everyone learned, and its three traps

VLOOKUP looks down the first column of a table for your key and returns a value from the column you number: =VLOOKUP(key, table, col_number, FALSE). It's everywhere, it's muscle memory, and it has three failure modes that quietly put wrong numbers on board slides:

• It counts columns. That 3 is a hard-coded position. Insert a column anywhere to its left and the 3 now points at the wrong field — no error, just a bad number.
• It can't look left. The key must sit in the leftmost column of the range. Need the value that lives to the left of your key? VLOOKUP simply can't.
• It defaults to an approximate match. Forget the final FALSE and VLOOKUP returns the closest match on unsorted data — which is to say, a random-looking wrong answer.

INDEX/MATCH: the classic fix that still wins two-way

INDEX / MATCH splits the job in two — =INDEX(return_range, MATCH(key, lookup_range, 0)). MATCH finds which row your key is in, and INDEX returns the value from that row in any column you point at. Because the return column is a real range — not a counted number — inserting columns doesn't break it, and it can happily return a value to the left of the key.

INDEX MATCH MATCH: the two-way lookup nothing else does simply

This is the one people mean when they say INDEX MATCH MATCH — two MATCHes inside one INDEX, one running down the rows and one running across the columns, to pinpoint a single cell in a grid. =INDEX(grid, MATCH(row_key, row_labels, 0), MATCH(col_key, col_labels, 0)). It's the natural tool for anything shaped like a matrix: a metric by product-by-quarter, a rate by tier-by-term, an actual by account-by-month. Change either label and the answer moves — no rebuilding, no VLOOKUP gymnastics.

XLOOKUP: the modern default that fixes all three traps

XLOOKUP is the one Microsoft built to retire VLOOKUP, and it does everything above without the sharp edges: =XLOOKUP(key, lookup_range, return_range, if_not_found). You point at real ranges instead of counting columns, so inserted columns never break it. It looks any direction, left or right. It defaults to an exact match — no forgotten FALSE. And the fourth argument is a built-in not-found answer, so #N/A never leaks onto a slide.

For a two-way lookup it nests cleanly — =XLOOKUP(row_key, rows, XLOOKUP(col_key, cols, grid)) — the same idea as INDEX MATCH MATCH, in one function name.

Side by side

So which one?

• Default to XLOOKUP. On any modern Excel or Google Sheets it's shorter, safer, and reads more clearly than the alternatives.
• Reach for INDEX MATCH MATCH when the data is a grid and you're looking up by row and column at once — it's still the cleanest way to express a two-way lookup.
• Keep VLOOKUP only for compatibility — a workbook that has to open on a version of Excel too old to know what XLOOKUP is. Always pass the final FALSE.

None of this is about memorizing four hundred functions. It's about wiring the right lookup into a layout you can actually read — the same discipline behind the dozen formulas a fractional CFO leans on.