Automatically Fill Excel Descriptions and Prices from a Drop-Down List
Question details
Automatically display a product description and unit price based on a product number selected from a drop-down list.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Product numbers are selected from a data-validation list, while the product details are stored on another worksheet.
- Observed behavior
- Selecting a product number should populate the corresponding description and unit price.
On Sheet2, place product numbers in column A, descriptions in column B, and prices in column C. Confirm that the product number selected in A2 matches an entry in Sheet2, including whether it is stored as text or a number.
Use VLOOKUP to Fill the Description and Price
Use these formulas when the product number is in A2 and the lookup table is on Sheet2.
Use the data-validation drop-down in cell A2 to select a product number.
Click the description cell, such as B2, and enter =VLOOKUP(A2,Sheet2!A:B,2,FALSE).
Click the unit price cell, such as C2, and enter =VLOOKUP(A2,Sheet2!A:C,3,FALSE).
Select the price cell and choose a currency format from the Home tab. If other rows have product drop-downs in column A, drag the formula cells' fill handles down to those rows.

Fill Product Details Automatically in WPS Spreadsheets
Use the same VLOOKUP formulas in WPS Spreadsheets to retrieve descriptions and prices from a product table on another worksheet.
- 1. Open your workbook: Open the Excel workbook in WPS Spreadsheets and check that Sheet2 contains product numbers, descriptions, and prices in columns A, B, and C.
- 2. Select a product number: On the entry sheet, choose a product number from the existing drop-down in A2.
- 3. Add the description formula: Click B2 and enter =VLOOKUP(A2,Sheet2!A:B,2,FALSE).
- 4. Add the price formula: Click C2 and enter =VLOOKUP(A2,Sheet2!A:C,3,FALSE), then use the Home tab to format the result as currency.

Frequently Asked Questions
Why does the formula return #N/A?
VLOOKUP returns #N/A when it cannot find an exact match. Check that the value in A2 appears in Sheet2 column A and that both values are stored consistently as text or numbers.
Can I use XLOOKUP instead of VLOOKUP?
Yes. In Excel versions that support XLOOKUP, enter =XLOOKUP(A2,Sheet2!A:A,Sheet2!B:B) for the description and =XLOOKUP(A2,Sheet2!A:A,Sheet2!C:C) for the price.
How do I apply the formulas to more product rows?
Select the description and price formula cells and drag their fill handles down. Excel adjusts the A2 reference for each row while continuing to look up values on Sheet2.
What if my lookup sheet has a different name?
Replace Sheet2 in each formula with your worksheet name. If the name contains spaces, enclose it in single quotes, as in =VLOOKUP(A2,'Product List'!A:B,2,FALSE).




