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
Finds a value in one range and returns the matching value from another — in any direction.
Syntax
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Lookup & Reference. Available from Excel 2021.
A worked example
=XLOOKUP(B2,$F$2:$F$6,$G$2:$G$6,"Unassigned")
Returns West
Looks up the customer id and returns the region, falling back to a readable message when nothing matches.
Where it goes wrong
Leaving the ranges relative. It works on the first row, then shifts when filled down and starts returning #N/A for the earliest records.
Practise XLOOKUP
Knowing what a function does and being able to write it from memory are different skills. These close the gap.
All XLOOKUP practice exercises →
- 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