Lookup Functions
Advanced
XLOOKUP Function
The lookup that handles a missing value without a second function wrapped around it.
Task:
Two product IDs came back from a stocktake, and one of them is not in the catalogue at all. Look up the name in column B and the price in column C, and show Not Found for the ID that has no match rather than letting an error through.
Learning Objectives:
- Learn XLOOKUP function
- Understand modern lookup techniques
- Practice versatile data retrieval
Hints
Solve without hints for +5 XP
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product ID | Product Name | Category | Price |
| 2 | P001 | Laptop | Electronics | 899 |
| 3 | P002 | Chair | Furniture | 149 |
| 4 | P003 | Phone | Electronics | 599 |
| 5 | P004 | Desk | Furniture | 299 |
| 6 | ||||
| 7 | Search ID | Name | Price | |
| 8 | P002 | |||
| 9 | P005 |
What this exercise teaches (contains the answer)
XLOOKUP is Excel's modern lookup function that combines the best of VLOOKUP, HLOOKUP, and INDEX-MATCH with additional features like built-in error handling.