2.3 Lookups: XLOOKUP, INDEX/MATCH and VLOOKUP
You can join two lists on a shared key and know why the misses are missing.
Before:01. Excel — First StepsUnlocks:03. Excel — Analysis
Joining two lists on a shared key is the single most used Excel skill in real work, and this topic covers XLOOKUP as the modern answer, INDEX with MATCH as the flexible one, and VLOOKUP because inherited workbooks are full of it. It sits at the centre of the formula module because lookups are where formulas start reaching across sheets. Most of the learning is in diagnosing the misses: an N A error usually means a trailing space or a number stored as text rather than a truly absent value, and telling those apart is the skill.
Work through these
XLOOKUP: exact match, not-found value, search direction
The modern lookup function takes a value, finds it in one range, and returns the matching item from another. It also takes a defined answer for when nothing matches. Setting that not-found value deliberately is what stops errors spreading.
INDEX/MATCH and why it still matters
The older two-function combination still appears everywhere and handles cases the single function does not. Being able to read it matters even if you never write a new one.
VLOOKUP: what it does, why it breaks, when you will still meet it
The oldest lookup function is fragile in specific ways: it looks rightward only and counts columns by position. You will meet it in inherited files, so understanding its failure modes is practical rather than historical.
Diagnosing #N/A: trailing spaces, text-vs-number, real absence
A not-found result usually means one of three things: invisible spaces, a number stored as text, or the value genuinely being absent. Working through them in that order finds it almost every time.
Practice file
- xl-04-lookup-and-join.xlsx
Two sheets that share a key, with deliberate trailing spaces, a text-vs-number mismatch and three genuine misses.
Opens in Excel. Every name, number and address in these files is made up. No account needed to download.
Sign in to keep your progress.
Free resources
We haven't checked most of these for screen reader use yet.
Links last checked 29 Aug 2026.
Stuck here?
Ask a mentor. A real person answers, and they can see exactly which topic you're on. Usually within a couple of working days.
Checking your session…
Topics shown in module order.