Lookup formula page
XLOOKUP Not Found Formula
Use the if-not-found argument when dashboards should show a clean fallback instead of #N/A.
Best for
Add a not-found fallback to XLOOKUP.
What it returns
If F2 does not exist in A2:A100, the formula returns Check SKU.
Copy formulas
=XLOOKUP(F2, A2:A100, D2:D100, "Check SKU", 0)=XLOOKUP(F2, A2:A100, D2:D100, "Check SKU", 0)Excel and Google Sheets use the same fourth XLOOKUP argument for a custom no-match result. The fallback can be fixed text, a blank, or a cell reference.
Example data
| SKU | Item | Category | Price | Stock |
|---|---|---|---|---|
| A-100 | Keyboard | Hardware | 49 | 18 |
| A-101 | Mouse | Hardware | 25 | 32 |
| B-200 | Desk Mat | Office | 18 | 9 |
| B-201 | Notebook | Office | 7 | 64 |
What it returns
If F2 does not exist in A2:A100, the formula returns Check SKU.
How the formula works
- The fourth XLOOKUP argument is the fallback result.
- Exact match keeps IDs safe.
- Use a short message that tells the user what to check.
| Syntax piece | Role in the formula |
|---|---|
| F2 | The key to search for. |
| A2:A100 and D2:D100 | The aligned lookup and return arrays. |
| "Check SKU" | The fourth argument returned when the key is missing. |
| 0 | The exact-match mode. |
Verified examples
=XLOOKUP(F2, A2:A100, D2:D100, "Check SKU", 0)Excel: Enter C-999 in F2. Returns: Check SKU
=XLOOKUP(F2,A2:A100,D2:D100,"Check SKU",0)Google Sheets: Enter B-201 in F2. Returns: 7
=XLOOKUP(F2,A2:A100,D2:D100,G2,0)Excel: Enter C-999 in F2 and Ask inventory team in G2. Returns: Ask inventory team
Common errors and fixes
| Issue | Likely cause | Fix |
|---|---|---|
| A blank F2 shows the fallback | XLOOKUP treats an empty input as a key with no match. | Add an input guard such as =IF(F2="","",XLOOKUP(...)) when blank input should stay blank. |
| XLOOKUP reports an array-size error | The lookup and return arrays cover different row counts. | Make both arrays start and end on the same rows. |
| The fallback hides a missing key | Custom text makes no-match output readable but does not identify why the key is absent. | Validate the source list separately and choose fallback wording that fits the workflow. |
When not to use this formula
- Do not use a friendly fallback as a substitute for validating duplicate keys or incorrect source data.
Alternatives
| Alternative | When to use it |
|---|---|
| XLOOKUP Exact Match Formula | Use when the standard Not found fallback is sufficient. |
| XLOOKUP Formula Builder | Use when lookup, return, and fallback cells need to be selected interactively. |
Related formulas
Official references
- XLOOKUP function from Microsoft
FAQ
Which XLOOKUP argument controls the no-match output?
The fourth argument controls what is returned when the lookup key is not found.
How do I keep a blank input blank?
Wrap the lookup in an IF guard such as =IF(F2="","",XLOOKUP(...)).