Function Problems

How to Auto-Fill Excel Cells from a Part Number, Name, or SKU

Algirdas Jasaitis Oct 1, 2026 868 views

Question details

The user wants to automatically populate corresponding product details (such as Part Name or SKU) into adjacent cells when manually entering a known value (such as a Part Number) into an Excel spreadsheet.

How to Auto-Fill Excel Cells from a Part Number, Name, or SKU

Product: Spreadsheet
Device & OS: not provided
Scenario: Setting up an inventory lookup table, invoice, or order form where entering a unique identifier like a Part Number, Part Name, or SKU instantly auto-fills the remaining fields.
Observed behavior: The user needs a dynamic formula solution to retrieve and display associated values from a master reference table based on varying user inputs.

Before you start

Ensure you have a complete master reference table prepared that contains all your Part Numbers, Part Names, and SKUs organized in separate, clearly labeled columns without any duplicate entries.

Solution 1Recommended

Use VLOOKUP and IFERROR Functions

This is the most straightforward method for looking up a unique identifier (like a Part Number) and retrieving data from columns to its right.

VLOOKUP searches for a specified value in the first column of a table array and returns a value in the same row from another column. By wrapping it in an IFERROR function, you can keep the cell blank instead of displaying an error if the Part Number hasn't been entered yet.

Select the target cell

Click on the cell where you want the Part Name or SKU to automatically appear (for example, B2).

Enter the formula

Type the formula: =IFERROR(VLOOKUP(A2, ReferenceTable, 2, FALSE), ""). Replace 'A2' with your input cell, 'ReferenceTable' with your master data range, and '2' with the column index number containing the Part Name.

Apply to multiple rows

Press Enter. Then, click and drag the fill handle (the small square at the bottom-right of the cell) down the column to apply the formula to other rows.

Column Index Number: The column index number is the position of the column in your reference table. If Part Number is column 1, and Part Name is column 2, you use 2.

Solution 2

Use INDEX and MATCH for Flexible Lookups

A robust alternative to VLOOKUP, especially useful when your lookup value (like SKU) is not located in the very first column of your master reference table.

Solution 3

Create a Data Validation Drop-Down List

Combine your lookup formulas with a drop-down list to prevent typing errors and ensure exact matches when selecting a Part Name or SKU.

Powerful Data Processing

Easily Auto-Fill and Manage Inventory Data in WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like VLOOKUP, INDEX, MATCH, and Data Validation, making it incredibly easy to build dynamic inventory systems, invoices, and order forms.

1. Open your data file: Launch WPS Spreadsheet and open your inventory or order tracking workbook.
2. Insert the lookup formula: Click on your target cell and easily type your VLOOKUP or INDEX/MATCH formula, utilizing the built-in formula prompts.
3. Apply across your sheet: Double-click the bottom-right corner of the cell to instantly flash-fill the logic down to all your data rows.

100% compatible with Microsoft Excel formulas, functions, and formats (.xlsx)Free to use for everyday data lookup and spreadsheet management tasksIntuitive, tabbed interface for seamless navigation between reference sheetsLightweight software that processes large data tables quickly and smoothly

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VLOOKUP formula return an #N/A error?

This usually happens when the specific Part Number or SKU you entered cannot be found in the reference table. It may be due to a typo, extra spaces, or the item missing from your master list. Wrapping your formula in an IFERROR function will display a blank cell or custom text instead of the error.

Can I look up a value if the SKU column is to the left of the Part Name column?

Standard VLOOKUP only searches the first column of the specified range and returns values to the right. To look leftwards across a table, you must use the INDEX and MATCH combination instead, which provides multi-directional lookup capabilities.

How do I make the formula work dynamically if the user chooses to input EITHER a Part Number, a Name, or a SKU?

If a single cell is meant to receive user input and auto-fill the rest, you must use dedicated input columns for each type (one for Number, one for Name, one for SKU). Alternatively, you can use complex nested IF statements to check which input cell contains data and trigger the corresponding lookup formula, avoiding circular reference errors.

Excel & Data Problems