How can I look up an item’s description from its unique inventory code?
Quick answer
Keep inventory codes and descriptions in separate columns, with each code and its description on the same row. In Excel, use XLOOKUP to search the code column and return the description from the matching row. Add a not-found message, then check that the code is in the searched range and that codes are unique. XLOOKUP returns the first match it finds. [S1]
The question, the details, the next step.
I have an inventory code and a list of item codes with descriptions. What’s the basic way to look up the description?
Use the inventory code as the value to search for, search in the table’s code column, and return the description from the same row. For example, if the code you want to find is in E2, codes are in column A, and descriptions are in column B, use:
=XLOOKUP(E2,A:A,B:B,"No match")
XLOOKUP searches one range and returns the corresponding item from another. Exact match is the default, and the fourth argument provides a message when no match is found. [S1] This example assumes your codes and descriptions are in those columns and your Excel version supports XLOOKUP.
What should I check if the formula says “No match” even though I think the code is in the table?
First, compare the value in the lookup cell with the code in the source table. Confirm that you selected the intended code column and that the searched range includes the row where the item appears. Check for differences such as an extra space, a missing character, or different punctuation. Also confirm that the code is actually present in the source table. A lookup can return only a description from the range it searches, so “No match” does not establish whether the item exists elsewhere.
How do I keep the source table organized and make sure a result is trustworthy if codes might be duplicated?
Keep each code and its description on the same row, use clear column headings, and make sure the searched ranges cover the intended records. Check that each inventory code appears only once. XLOOKUP returns the first match it finds, so a duplicate can hide other rows with the same code. [S1] Review duplicates and decide which row is authoritative before relying on the result. The lookup itself does not determine which duplicate record is correct; that depends on your inventory data.
Can you show a small example and give me a reusable setup checklist?
Suppose your source table has columns headed Code and Description, and one row contains BX-104 and Storage bin. If E2 contains BX-104, and codes are in A while descriptions are in B, =XLOOKUP(E2,A:A,B:B,"No match") returns the description from the matching row. If E2 contains a code that is absent from the searched range, the formula displays No match. Adapt the references to your table and check that they cover the intended records.
Before relying on the result, try a code you can identify in the source table and one you expect to be absent. XLOOKUP is not available in Excel 2016 or Excel 2019. [S1] Check which lookup options your spreadsheet version supports if you use one of those editions.
Setup checklist:
- Put codes and descriptions in clearly headed columns, with each record on one row.
- Identify the lookup cell, code range, and description range.
- Use a clear not-found message and verify the ranges cover the intended records.
- Check for duplicate codes and resolve them before relying on results.
- If there is no match, inspect the entered code, range boundaries, and source table.
A practical checklist
Keep each code and its description together on one row under clear column headings.
Identify the lookup cell and the code and description ranges.
Use a not-found message and ensure both ranges cover the intended records.
Check for duplicate codes and resolve them before relying on results.
For a missing match, inspect the entered code, range boundaries, and source table.
Try a known code and a code expected to be absent.
A few more useful answers
Does XLOOKUP require an approximate match for inventory codes?
No. Exact match is the default for XLOOKUP, so the example uses that behavior without an extra match-mode argument. [S1]
What does “No match” tell me?
It means the lookup did not find the code in the range it searched. Check the code and range, then confirm whether the record is in the source table.
What if the same inventory code appears more than once?
XLOOKUP returns the first match it finds. [S1] Review duplicate rows and determine which record should be authoritative before relying on the returned description.
Can I use this formula in Excel 2016 or Excel 2019?
XLOOKUP is not available in Excel 2016 or Excel 2019. [S1] Check the lookup options supported by your spreadsheet version.
Sources
References used for this guide.
- [S1] XLOOKUP function in Excel support.microsoft.com · Checked