Practical technology learning
Excel XLOOKUP: Keep a Missing Match Separate from a Valid Zero
A missing key can become a misleading business number when a workbook silently replaces every lookup problem with zero. This small exercise uses fictional item codes and an explicit not-found message. It keeps absence of a record separate from a legitimate numeric value.
Use a deliberately small source
Create two records: P01 has 0 units and P02 has 7. Ensure the codes are unique and the quantities are populated numbers. Set aside P03 as a lookup request that is not present in the source. These three cases make an independent expected-results sheet possible.
Make the lookup contract visible
In an Excel version supporting XLOOKUP, look up each request against the code column and return the corresponding units. Supply Not found as the if_not_found value. XLOOKUP defaults to exact matching; an explicit exact-match choice can also help communicate the intended rule during review.
Compare the three expected results
P01 should return the valid number 0, P02 should return 7, and P03 should return the chosen Not found text. Check the lookup and return ranges as a pair. The expected zero is evidence that a matching record exists with no units, not evidence that the formula failed.
Add harder data only after this passes
A blank source quantity needs a policy of its own, as does a duplicated item code. Do not infer that the first exercise correctly handles those conditions. Add each case separately and decide whether the report should reject, flag or intentionally select such a record.
Review the failure-handling boundary
- Use a meaningful not-found label for an absent key.
- Keep numeric zero distinct from missing or unavailable data.
- When appropriate, IFNA handles #N/A specifically; it is not a reason to hide every error.
- Check function support and formula behavior in the actual Excel version used.
Keep the lookup review reproducible
Save the two-record source, three requests and expected outputs together. Lookup functions and reporting are part of the Mango Engineers Excel and Advanced Excel learning path. The exercise gives you a compact way to discuss missing records before adding a larger dashboard or report.