Automatically Fill Product Names from Codes in Excel
Question details
Automatically return a product name in column E when a product code is entered in column D.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains a lookup table of unique product codes and their corresponding names.
- Observed behavior
- Entering a code should display its matching product name, while unmatched codes should leave the result blank.
Place unique product codes in column A and their matching names in column B on the same worksheet. Check that codes in columns A and D use consistent formatting, including any leading zeros.
Look up product names with INDEX and MATCH
Use this formula to fill column E from a code-to-name table in columns A and B.
Enter product codes in column A and the corresponding product names in column B, with each pair on the same row.
Select cell E2 and enter =IF(D2="","",IFERROR(INDEX(B:B,MATCH(D2,A:A,0)),"")), then press Enter.
Select E2 and drag its fill handle down column E to the rows where you will enter product codes.
Enter a code from column A in D2. The matching name from column B should appear in E2.

Fill Product Names from Codes in WPS Spreadsheets
Use the same INDEX and MATCH formula in WPS Spreadsheets to return product names automatically as you enter codes.
- 1. Open your worksheet: Open the workbook in WPS Spreadsheets and place unique product codes in column A and matching names in column B.
- 2. Add the lookup formula: Select E2 and enter =IF(D2="","",IFERROR(INDEX(B:B,MATCH(D2,A:A,0)),"")), then press Enter.
- 3. Copy the formula: Drag the fill handle from E2 down column E to cover the rows where you will enter codes.
- 4. Enter a code: Type a product code in column D and check that the matching name appears in column E.

Frequently Asked Questions
Why is the product name cell blank?
The formula returns a blank if the code in column D is empty or has no match in column A. Check for typos, extra spaces, and differences in text or number formatting.
Can I display a message when a code is not found?
Yes. In E2, replace the final empty string in IFERROR with a message: =IF(D2="","",IFERROR(INDEX(B:B,MATCH(D2,A:A,0)),"Product Not Found")).
What happens if a product code appears more than once?
MATCH returns the first matching code in column A, so the formula displays the name from that row. Keep codes unique if each code should identify one product.
Can Excel AutoComplete match a code to a product name?
No. AutoComplete suggests previously entered cell text; it does not look up a corresponding name in another column. Use the INDEX and MATCH formula instead.




