Lookups·Lesson 30 of 62Pro
How to handle a missing match in XLOOKUP in Google Sheets
Handle a missing match
How XLOOKUP works
XLOOKUP takes an optional 4th argument: what to show when nothing matches.
XLOOKUP(what, where, return, if not found)
Example on this data
=XLOOKUP(2000,A2:A11,C2:C11,"Missing")→Missing
There's no order 2000, so it shows the fallback.
What you’ll do
You’ll look up an order that isn’t in the table and show “No match” instead. Planning for missing values makes your lookups safe to share.
Try it
Type =XLOOKUP(2000,A2:A11,C2:C11,"Missing") into K2 and press Enter. Watch what the sheet does.
The result will appear in K2.
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | Rep | Region | Product | Units | Price | Order date | Ship date | |||
| 2 | 1001 | Ana | West | Desk | 3 | 240 | 1/5/2026 | 1/9/2026 | |||
| 3 | 1002 | Bo | East | Chair | 8 | 85 | 1/12/2026 | 1/20/2026 | |||
| 4 | 1003 | Cy | West | Lamp | 12 | 30 | 1/19/2026 | 1/22/2026 | |||
| 5 | 1004 | Di | North | Desk | 2 | 240 | 2/2/2026 | 2/13/2026 | |||
| 6 | 1005 | Ana | West | Chair | 5 | 85 | 2/9/2026 | 2/12/2026 | |||
| 7 | 1006 | Eve | South | Shelf | 4 | 120 | 2/16/2026 | 2/27/2026 | |||
| 8 | 1007 | Bo | East | Lamp | 20 | 30 | 3/2/2026 | 3/4/2026 | |||
| 9 | 1008 | Cy | West | Shelf | 1 | 120 | 3/9/2026 | 3/18/2026 | |||
| 10 | 1009 | Di | North | Chair | 6 | 85 | 3/16/2026 | 3/19/2026 | |||
| 11 | 1010 | Eve | South | Desk | 7 | 240 | 3/23/2026 | 4/1/2026 |
Ctrl+/ shows the shortcuts
Drill locked
Unlock the other 44 lessons for $49.99
You’ve seen how XLOOKUP works. Pro lets you write it yourself on real data and get checked instantly, with practice sets and section reviews for every lesson.
Your drill
In K2, find the rep for order 1099 and show "No match" if it doesn't exist.
One payment, no subscription.