Function Problems

How to Automatically Populate External Part Numbers in Excel

Maira Mehtab Sep 22, 2026 871 views

Question details

The user needs to automatically retrieve and display an external part number in a specific column whenever an internal part number is entered in an adjacent column.

Product: Excel
Device & OS: not provided
Scenario: Mapping inventory or mapping part numbers between internal systems and external vendors.
Observed behavior: Column D needs to automatically fetch matching data from Column B based on input in Column C, referencing a master list contained in Columns A and B.

Before you start

Ensure your master list of internal and external part numbers in Columns A and B does not contain duplicate internal part numbers, as lookup functions will only return the first exact match they find.

Solution 1Recommended

Use the VLOOKUP Function with an IF Statement

This is the most universally compatible method to automatically fetch matching part numbers while keeping empty rows completely blank.

Combining VLOOKUP with an IF statement ensures that your destination cell remains neat and empty until an internal part number is actually entered.

Select the target cell

Click on cell D2, where you want the external part number to automatically appear.

Enter the VLOOKUP formula

Type the formula =IF(C2<>"",VLOOKUP(C2,A:B,2,FALSE),"") into the formula bar and press Enter.

Apply to the entire column

Click the fill handle (the small square at the bottom-right corner of cell D2) and drag it down to apply the formula to the rest of the cells in column D.

Formula Breakdown: The IF function checks if C2 is empty. If it contains data, VLOOKUP searches for the exact match (FALSE) of C2 within columns A and B, and returns the corresponding value from the 2nd column (B).

Solution 2

Use the XLOOKUP Function (Newer Excel Versions)

If you are using a newer version of Excel or WPS Office, XLOOKUP provides a simpler and more robust way to achieve the same result without needing nested IF statements.

Efficient Spreadsheet Management

Automate Data Lookups Easily in WPS Spreadsheet

WPS Office fully supports advanced functions like VLOOKUP, XLOOKUP, and IF statements, making it simple to map internal and external part numbers automatically. It easily handles large inventory datasets without slowing down your computer.

1. Open your inventory spreadsheet: Launch WPS Spreadsheet and open the file containing your internal and external part numbers.
2. Input the lookup formula: In your target column (e.g., D2), type your preferred lookup formula, such as =VLOOKUP(C2,A:B,2,FALSE).
3. Drag to autofill: Double-click or drag the fill handle at the bottom right of the cell to populate the remaining cells instantly.

Fully compatible with Microsoft Excel (.xlsx, .xls) files and formulas.Seamless support for VLOOKUP, XLOOKUP, and other advanced data functions.Lightweight software that handles massive datasets smoothly.Free and intuitive tabbed interface.

Frequently Asked Questions

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

An #N/A error means the VLOOKUP function cannot find the internal part number from Column C in your master list (Column A). Check for extra trailing spaces, mismatched formatting (e.g., numbers formatted as text), or typos in your lookup value.

How can I display a custom message when a part number isn't found instead of an error?

You can wrap your VLOOKUP formula in the IFERROR function. For example: =IFERROR(VLOOKUP(C2,A:B,2,FALSE), "Not Found"). This will display the phrase 'Not Found' instead of an #N/A error if the internal part number doesn't exist.

Can I use these formulas if my master list of part numbers is on a different worksheet?

Yes. You simply need to reference the other sheet in your formula. For example, if your master list is on a sheet named 'MasterData', your formula would look like this: =IF(C2<>"",VLOOKUP(C2,MasterData!A:B,2,FALSE),"").

Does WPS Spreadsheet support the XLOOKUP function?

Yes, the latest versions of WPS Office fully support the XLOOKUP function, allowing you to use it seamlessly to map data just as you would in Microsoft Excel.

Excel & Data Problems