Function Problems

Automatically Fill Product Names from Codes in Excel

Ayan MasoodAyan Masood Nov 14, 2026 868 views

Question details

Automatically return a product name in column E when a product code is entered in column D.

Automatically Fill Product Names from Codes in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Create the lookup table

Enter product codes in column A and the corresponding product names in column B, with each pair on the same row.

2
Enter the formula

Select cell E2 and enter =IF(D2="","",IFERROR(INDEX(B:B,MATCH(D2,A:A,0)),"")), then press Enter.

3
Fill the formula down

Select E2 and drag its fill handle down column E to the rows where you will enter product codes.

4
Test a product code

Enter a code from column A in D2. The matching name from column B should appear in E2.

Look up product names with INDEX and MATCH
Unmatched codes: The formula leaves the result blank if D2 is empty or the code is not found. MATCH returns the first match if a code appears more than once.
WPS Spreadsheets

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. 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. 2. Add the lookup formula: Select E2 and enter =IF(D2="","",IFERROR(INDEX(B:B,MATCH(D2,A:A,0)),"")), then press Enter.
  3. 3. Copy the formula: Drag the fill handle from E2 down column E to cover the rows where you will enter codes.
  4. 4. Enter a code: Type a product code in column D and check that the matching name appears in column E.
Match product codes to names with familiar spreadsheet formulasOpen and edit Microsoft Excel XLSX and XLS filesFill formulas down a column for multiple product entriesUse a familiar spreadsheet interface
QA img-9

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.