About This Article
This article was created using an automated generation workflow powered by generative AI. We reviewed the Microsoft XLOOKUP specifications and organized them into a minimal example to observe unfound values without leaving them as errors.
Verification Status: 📘 Confirmed with Microsoft official specifications, not verified on actual Excel hardware
By using the fourth argument of XLOOKUP [if_not_found], you can change unregistered data from #N/A to an easier-to-read display.
Try It First
Enter A001,A002,A003 in A2:A4, 東京,大阪,福岡 in B2:B4, and A004 in D2.
=XLOOKUP(D2,A2:A4,B2:B4,"未登録")
Look Here
Verify that the result is 未登録. This is not a successfully retrieved value, but rather a state indicating "no match was found."
Why?
The fourth argument is the return value when the lookup value is not found. Since leaving it as an empty string makes it easy to overlook, displaying a meaningful string during verification makes it easier to observe the state.
Change One Thing
Change D2 to A002. This time, 大阪 is returned, allowing you to visually compare the difference between unregistered and registered items.
For Practical Work
When reconciling employee IDs, product codes, location codes, etc., this makes it easier to filter out unregistered items later. However, whether to keep "Unregistered" in the final report or convert it into an error flag in a separate column depends on your business rules.
