VLOOKUP → XLOOKUP

How this deck works

Prerequisite knowledge

Meet your two datasets

One skill, two worlds. If you can make a formula work in both, you actually understand it — that's transfer, and transfer is what lets you teach it.

VLOOKUP anatomy — a quick refresher

The five pains of VLOOKUP

You've probably hit every one of these. None of them are your fault — they're baked into the function.

XLOOKUP, one argument at a time

Head-to-head: same task, fewer traps

The task: Talia Rourke's goal count, from her ID.

The sentence you'll teach

Every XLOOKUP — no matter how fancy — is these three plain-English chunks:

Named ranges read like English

Both formulas below give the same answer. One of them you can read over the phone.

Worked example: ID → name

Now reverse it — look left

Fail gracefully: if_not_found

Look up an ID that doesn't exist — HR-999 — and watch the fourth argument earn its keep.

First, a new law of physics: the dynamic array

Anatomy of a spill — four words to own

When spill breaks: #SPILL!

The #1 spill confusion, fully unpacked: something is sitting in the way of where the answers need to go.

The payoff: three answers from one formula

Context switch: the phrasebook

Same skill, new world. tblVocab holds five words with translations in three scripts — plus pinyin, a category, and a mastery score out of 100.

One word, three scripts, one formula

It works in any direction — even across scripts

Wildcards: match_mode 2

Everything so far needed the exact text. Meet match_mode — XLOOKUP's fifth argument, which sets how strictly the value has to match. Mode 2 switches on wildcards: you only remember the word starts with "hel…"? Good enough.

match_mode — how to match

You just used mode 2 — here's the whole menu. One question decides which you need: what should count as a match when the exact value isn't in the list? Leave the argument out for exact; reach for the others when "close" is the point.

Approximate match, done right

Score → band, in both worlds. A band table lists the minimum score for each band, so your exact score is usually not in it — and an exact match would fail. match_mode -1 says what VLOOKUP's fragile TRUE only implied: exact, or the next smaller value — which lands you in the band you're inside.

search_mode — where to start looking

The sixth and final argument does a different job: it changes nothing about what counts as a match, only where Excel starts reading the column. That matters exactly when a value appears more than once — XLOOKUP returns the first match it meets, so direction decides which one wins.

"How did we go last time against New Zealand?"

We've played New Zealand twice, so the Opponent column holds two matches. A normal top-down search stops at the March game and never sees June's. Scanning bottom-up with search_mode -1 makes the last matching row win — and in any log that grows downwards, the last row is the most recent.

The party trick: nested, two-table lookup

Where is Talia's home base? Her row says NT — but "Darwin" lives in a different table. Chain two XLOOKUPs: the inner answer becomes the outer question.

It replaces HLOOKUP too

Point the arrays at rows instead of columns and the same three-chunk sentence works sideways. You can retire a second function today.

The cheat sheet — screenshot this one 📸

When it goes wrong — the five usual suspects

Now teach it