Wauvel
Toolkit

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

← All posts
By Blake EkelundJuly 1, 2026 · 7 min read

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.

The one-line answer: if your Excel is 2021-or-later or Microsoft 365 (or you're in Google Sheets), use XLOOKUP for almost everything. Keep INDEX/MATCH for two-way lookups and older files; keep VLOOKUP only when you have to hand the file to someone stuck on a decade-old 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.
VLOOKUP
B6=VLOOKUP(101, A2:C4, 3, FALSE)
ABC
1SKUProductPrice
2100Widget$42
3101Gadget$55
4102Gizmo$90
5
6Price for 101$55
The 3 is a hard-coded column count. Insert a column before Price and VLOOKUP happily returns the wrong one — silently.

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
B6=INDEX(A2:A4, MATCH(101, B2:B4, 0))
AB
1ProductSKU
2Widget100
3Gadget101
4Gizmo102
5
6Product for 101Gadget
MATCH finds the row; INDEX returns from any column — including one to the left of the key, which VLOOKUP can't reach.

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.

INDEX / MATCH / MATCH
B7=INDEX(B2:D4, MATCH("Gadget", A2:A4, 0), MATCH("Q3", B1:D1, 0))
ABCD
1ProductQ1Q2Q3
2Widget404446
3Gadget222528
4Gizmo91112
5
6Gadget in Q328
Two MATCHes — one down the rows, one across the columns — pull a single cell out of a grid. Change either label and the answer follows.

Free · no account · no card

Get your 2027 budget built from your QuickBooks — P&L, balance sheet and cash flow, in Excel.

Build my 2027 budget →

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.

XLOOKUP
B6=XLOOKUP("A/P", A2:A4, B2:B4, "Not found")
AB
1AccountBalance
2Cash48,200
3A/R61,400
4Inventory92,800
5
6Look up A/PNot found
Exact match by default, and a built-in answer when the key isn't there — no #N/A to explain in a board meeting.

Side by side

VLOOKUPINDEX/MATCHXLOOKUP
Look left of the keyNoYesYes
Survives inserted columnsNoYesYes
Default matchApproxExactExact
Built-in not-foundNoNoYes
Two-way (row & column)NoMATCH MATCHNested
Runs on old Excel / SheetsYesYes2021+/365

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.

Every Wauvel report exports to Excel with the formulas live, not flattened to values — so the lookups and totals are real and auditable, not a pasted snapshot. And the free Excel tools come pre-filled with your own accounts and numbers. See a sample report →

Wauvel · Know your numbers. Every day.

Need your financials for a lender, a buyer or your accountant? Get the free financials pack →

See what an AI CFO says about your own numbers.

$99/mo, everything included. Free for 14 days, no card.

Meet your AI CFO →

Keep reading

ToolkitOctober 29, 2026 · 6 min read

The five reports to send your accountant at year end

Most year-end delays are one side waiting on the other. Sending the right five things unprompted, with the questions already flagged, turns a three-week back-and-forth into a single conversation - and usually costs less.

Read it →
ToolkitOctober 27, 2026 · 6 min read

Building a rolling 12-month forecast

A calendar-year forecast gets shorter every month until, by November, you're managing a six-week horizon. A rolling one always looks twelve months ahead - and it takes about an hour a month to maintain.

Read it →
ToolkitOctober 19, 2026 · 6 min read

The one KPI that matters most for your business model

Dashboards fail by treating every metric as equally important. Every business model has a single number that moves everything downstream - here's how to find yours, and why the wrong choice quietly steers you into bad decisions.

Read it →
ToolkitOctober 5, 2026 · 6 min read

The 13-week cash flow, rebuilt every Monday

Most cash forecasts are built once, admired, and never opened again. The value isn't in the model - it's in the twenty-minute weekly habit of rolling it forward and comparing what you predicted to what happened.

Read it →
ToolkitSeptember 27, 2026 · 8 min read

How to build a small business pro forma a lender will believe

A bank, a landlord, or a seller asks for a pro forma and most owners start from a blank spreadsheet. Start from your last twelve months instead, add the one change you're asking about, and check the number the lender will check first.

Read it →
ToolkitSeptember 9, 2026 · 7 min read

The month-end close that takes four hours, not four days

A slow close is almost never a bookkeeping speed problem - it's a sequencing problem. Here's the order that removes the waiting, the four reconciliations that catch nearly every error, and the rule that decides when the month is actually done.

Read it →
ToolkitSeptember 7, 2026 · 8 min read

Building next year's operating plan (start now, not in December)

An annual plan built in the last week of December is a wish list. Built in September, it is a decision you have time to act on. Here's the order to build one in, how long each layer takes, and the difference between a plan and a forecast.

Read it →
ToolkitSeptember 3, 2026 · 7 min read

The 10 KPIs worth watching weekly (and the 40 that aren't)

Most dashboards fail because they show everything, which is the same as showing nothing. Here are the ten numbers that earn a weekly look for a small business, why each one is on the list, and the test for whether a metric deserves your attention at all.

Read it →
ToolkitAugust 24, 2026 · 7 min read

Recurring, capacity, or funnel: which revenue engine are you?

Almost every business makes money in one of three shapes, and the shape determines which numbers you should be forecasting. Pick yours and you stop guessing at revenue - you start building it from drivers you can actually influence.

Read it →