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.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of XLOOKUP
0/3The same formula in the other shapes it takes at work.
- XLOOKUP Function
The lookup that handles a missing value without a second function wrapped around it.
CoreAdvanced - XLOOKUP to the left, with a default
Search one column, return from another on either side, and say what to show when nothing matches.
Intermediate - XLOOKUP with 0 as the fallback
A missing item should cost nothing in the total, not break it.
Intermediate
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 teachesMay contain 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.