Course outline / Lookup functions
Lesson 27 of 75 · Intermediate · 4 exercises
XLOOKUP in depth
Look left, handle missing values and find positions with XLOOKUP and XMATCH.
What you'll practise
- Look to the left
- A message when nothing matches
- Look up whatever is typed
- Find a position with XMATCH
Look to the left
VLOOKUP can only return columns to the right of the one it searches. XLOOKUP has no such limit, because you point at the two ranges separately.
In cell F2, find the price (A2:A5) of the product with code "C30" (C2:C5).
A message when nothing matches
XLOOKUP's fourth argument is what to show when nothing matches, so you don't need IFERROR.
In cell F2, find the product (B2:B5) for code "E50" (C2:C5), showing "No such code" if it is missing.
Look up whatever is typed
In cell F2, find the product (B2:B5) whose code is typed in E2, from C2:C5.
Find a position with XMATCH
XMATCH is the modern MATCH: it returns the position of a value, and matches exactly by default.
In cell F2, find the position of "Shelf" in B2:B5.
Try it in a real spreadsheet
Every step checks your answer instantly and tells you exactly what to fix.