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.
Your first month for $9.50 $19
50% off your first month when you join today.
Then $19/month. Cancel any time, and get a full refund within 14 days if it isn't for you.
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.