XLOOKUP with a not-found value
PremiumP-109 is not in the list. In F2, use XLOOKUP to return the price for E2, and use its fourth argument to show "Not found" instead of #N/A.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | SKU | Product | Price | SKU | Price | ||
| 2 | P-100 | Desk | 1499 | P-109 | |||
| 3 | P-101 | Chair | 899 | ||||
| 4 | P-102 | Lamp | 249 | ||||
| 5 | P-103 | Shelf | 649 | ||||
| 6 | P-104 | Monitor | 1799 | ||||
| 7 | |||||||
| 8 |
Premium lesson
Unlock XLOOKUP and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
It comes up in this mock test: Excel assessment (Indeed format)
Why this works
When XLOOKUP finds no match it returns #N/A, unless you give it an if_not_found value as the fourth argument. Unlike IFERROR, this only catches a missing match and still shows real mistakes such as a broken reference.
Common mistake: Wrapping XLOOKUP in IFERROR instead. That also hides real mistakes; the fourth argument only catches a value that isn't there.
The answer
- F2
=XLOOKUP(1E2,2A2:A6,3C2:C6,4"Not found")
In plain words: Find 1the SKU in E2 in 2A2:A6 and return 3its price from C2:C6, or 4Not found if it is not there.