Price a pick list against a supplier catalogue that no longer lists everything
XLOOKUP's fourth argument hands back whatever you tell it the moment nothing matches, so a missing part number never has to blow up into #N/A on a sheet someone else is reading.
Task:
You handle purchasing for Meridian Circuits, a small electronics assembler. The current price list for four active parts sits in rows 2 to 5. This week's pick list has four parts on it, but the supplier discontinued a couple of them since the price list was last updated. In C8, look up each part's unit price from the price list, showing "Discontinued" for any part number that no longer appears there. Copy down through C11.
Learning Objectives:
- Use XLOOKUP's fourth argument to supply a fallback instead of letting a missing match surface as #N/A
- Recognize when a lookup needs a built-in "not found" case rather than an IFERROR wrapped around it
- Keep a lookup table's range fixed with absolute references while the value being searched for changes down a column
Solve without hints for +5 XP
Interactive Spreadsheet
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 | |
|---|---|---|---|
| 1 | Part Number | Description | Unit Price |
| 2 | P-1001 | Bearing 6205 | 4.25 |
| 3 | P-1002 | Drive Belt | 12.8 |
| 4 | P-1003 | Gasket Set | 3.15 |
| 5 | P-1004 | Filter Cartridge | 7.6 |
| 6 | |||
| 7 | Order Part | Unit Price | |
| 8 | P-1002 | ||
| 9 | P-1005 | ||
| 10 | P-1003 | ||
| 11 | P-1006 |
What this exercise teaches (contains the answer)
XLOOKUP(A9,$A$2:$A$5,$C$2:$C$5,"Discontinued") searches for P-1005 in A2:A5 exactly the way VLOOKUP would, but P-1005 was dropped from the price list, so there's nothing in that range to match. Rather than surfacing #N/A the way VLOOKUP or a bare MATCH would, XLOOKUP falls through to its fourth argument and returns "Discontinued" instead — no IFERROR or IFNA wrapper required, because the fallback is built into the function itself. P-1002 and P-1003 are still in rows 3 and 4 of the price list, so those two rows match normally and return 12.8 and 3.15. The dollar signs pin the price list in place as the formula copies down through four different order rows, each one only changing which part number it searches for.