What you will fix
The lookup key in D2 is P03, but the source list in A2:A3 contains only P01 and P02. A plain XLOOKUP therefore has no matching row. You will verify the mismatch, return a useful fallback for the expected #N/A case, and confirm that a valid key still returns its price.
Learning objectives
Open the practice workbook
Practice in your own copy
Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.
Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.
Understand the sample
| SKU | Price | Lookup SKU | Expected result |
|---|---|---|---|
| P01 | 12 | P03 | Missing |
| P02 | 35 |
A2:B3 is the source lookup table. D2 contains the lookup key and E2 is the formula result. Column C is intentionally blank in the workbook to separate the source table from the lookup area.
Diagnose and fix the missing result
Step 1: Compare D2 with A2:A3. D2 contains P03, while the available source keys are P01 and P02. Because XLOOKUP searches for an exact match by default, P03 has no matching row and the underlying lookup produces #N/A.
Step 2: Select E2 and enter the formula below. XLOOKUP searches D2 in A2:A3 and returns the aligned value from B2:B3. IFNA changes only a #N/A result to the text Missing.
=IFNA(XLOOKUP(D2,A2:A3,B2:B3),"Missing")Step 3: Verify E2 shows Missing. Then temporarily change D2 from P03 to P02. The same formula should return 35, proving that the ranges are aligned and the fallback appears only when the key is absent. Restore D2 to P03 when finished.
Checkpoint
| Check | Expected value |
|---|---|
| Source keys A2:A3 | P01, P02 |
| Lookup key D2 | P03 |
| Formula cell E2 | Missing |
| Temporary test with D2 = P02 | 35 |
After the temporary test, restore D2 to P03 so the final workbook returns Missing in E2.
Common mistakes and fixes
The key really is missing: if D2 is not present in A2:A3, #N/A is the expected XLOOKUP result. Add or correct the source key only when the business data says it should exist; otherwise a deliberate fallback such as Missing is appropriate.
The lookup and result ranges are misaligned: A2:A3 and B2:B3 must cover corresponding rows and have compatible sizes. Do not shift one range to A2:A3 while the other starts at B3.
The values look identical but still do not match: imported text can contain extra spaces or inconsistent text/number types. Inspect the source and lookup key before adding error handling; IFNA should not be used to conceal dirty data.
Using IFERROR instead of IFNA can hide unrelated problems. For an expected no-match case, IFNA is narrower because it handles #N/A while leaving other errors visible for troubleshooting.
Independent practice
Try P01 in D2 and predict the result before pressing Enter: it should return 12. Then try a new key such as P99 and confirm the fallback is Missing. Restore P03 at the end.
Key takeaways
- ✓XLOOKUP returns #N/A when an exact lookup key is absent from the lookup range.
- ✓Check the key and both aligned ranges before wrapping a lookup in error handling.
- ✓IFNA is appropriate when you want a fallback specifically for #N/A.
- ✓With D2 = P02, the formula returns 35, confirming the lookup ranges work.
- ✓With the original D2 = P03, E2 correctly shows Missing.
Check your understanding
Answer four questions about the practice sheet.
Next lesson
Continue with the next troubleshooting lesson after you can tell the difference between a genuinely absent lookup key and a lookup configuration problem.
Comments
No comments yet. Be the first to join the discussion.