IntermediateLookup & Reference
Look up a customer's region with XLOOKUP
Match a customer ID against a reference table to return their region.
XLOOKUP
8 min25 points
XLOOKUP practice exercises
Finds a value in one range and returns the matching value from another — in any direction. Below are 6 interactive exercises that put it to work on realistic business data — you write the formula, and get told exactly what went wrong when it does not work.
Syntax
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Available from Excel 2021.
Need a refresher? Learn XLOOKUP — syntax, a worked example and where it goes wrong.
Practise XLOOKUP
Ordered from most approachable to hardest.
- AdvancedLookup & Reference
Lock ranges so a lookup survives fill-down
Use absolute references so a lookup does not break when copied down a column.
XLOOKUP
12 min40 points - AdvancedLookup & Reference
Handle missing lookups gracefully
Return a readable message instead of #N/A when a lookup finds nothing.
XLOOKUP
10 min40 points - AdvancedLookup & Reference
Chain two lookups with XLOOKUP
Look up a value that only exists two tables away from what you started with.
XLOOKUP
13 min40 points - IntermediateLookup & Reference
When can VLOOKUP not do the job?
Identify the one situation VLOOKUP genuinely cannot handle.
VLOOKUP · XLOOKUP
3 min20 points - AdvancedData Cleaning
Why does a lookup fail on clean data?
Diagnose a match failure where both values look identical on screen.
XLOOKUP
4 min30 points
When XLOOKUP is the right tool
Finds a value in one range and returns the matching value from another — in any direction. It belongs to the lookup & reference family, and it shows up constantly in real work — which is why it is worth being genuinely fluent rather than merely familiar.
Before this one, it helps to be comfortable with VLOOKUP. The exercises below assume that ground is covered.
Reading about a function is not the same as being able to write it. The exercises on this page exist because the gap between recognising XLOOKUP and producing it from memory, correctly, on data you have not seen before, only closes by doing it.
Related functions
Questions about XLOOKUP
How do I practise XLOOKUP without Excel installed?
Every exercise on this page runs in your browser. You type the XLOOKUP formula into a real spreadsheet grid and get told immediately whether it works — no download, no account.
What does XLOOKUP do?
Finds a value in one range and returns the matching value from another — in any direction. The syntax is XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). The exercises below put it to work on realistic business data rather than abstract examples.
How long does it take to learn XLOOKUP?
Most people can write a working XLOOKUP formula within twenty minutes of practice. Getting it right under pressure — with edge cases, unsorted data and ranges that must survive being filled down — is what the harder exercises here are for.