XLOOKUP practice exercises
XLOOKUP searches one range and returns the matching item from another. Unlike VLOOKUP it can look in any direction, uses an exact match by default and lets you choose what to show when nothing is found.
Syntax
XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])
search_key- The value to find.
lookup_range- The single column (or row) to search.
result_range- The column to return from. It must be the same size as lookup_range.
missing_value- Optional. What to return instead of
#N/Awhen nothing matches.
The practice data
A product list with SKU, name, price and stock. F2 holds a SKU to look up. Every exercise below uses this sheet, and your answer goes in H2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | SKU | Product | Price | Stock | SKU to find | |
| 2 | 101 | Widget | 5 | 40 | 103 | |
| 3 | 102 | Gadget | 20 | 15 | ||
| 4 | 103 | Gizmo | 50 | 0 | ||
| 5 | 104 | Doohickey | 12 | 8 | ||
| 6 | 105 | Sprocket | 3 | 120 |
Worked example
=XLOOKUP("Gadget",B2:B6,C2:C6)
Searches the Product column for "Gadget" and returns the matching Price. Result:
| 20 |
Your turn: 3 exercises
Type a formula and press Enter. It is checked by a real formula engine, so any correct approach counts.
1 Return the price of "Sprocket".
2 Return the SKU of "Gizmo". The SKU column is to the left of the product names.
3 Return the stock of SKU 999, showing Not found if it doesn't exist.
Master XLOOKUP in the full course
Lesson 11 of 18 goes deeper, with more exercises in a real spreadsheet grid. Start with the first 4 lessons free, then unlock everything with Pro.
Start free See pricingCommon XLOOKUP mistakes
- Using ranges of different sizes for lookup_range and result_range, which gives an error.
- Selecting the whole table as lookup_range. XLOOKUP needs a single column (or row) to search.
- Forgetting the missing_value argument and then wrapping everything in IFERROR, which also hides real errors.
FAQ
Is XLOOKUP available in Google Sheets?
Yes. Google Sheets added XLOOKUP in 2022 and it works the same way as in Excel 365.
Can XLOOKUP return several columns at once?
Yes. If result_range is several columns wide, the whole matching row spills to the right.