How to Create a Product Drop-Down and Retrieve Price in Excel
Question details
The user wants to create a drop-down menu for selecting products and automatically fetch the corresponding price from a separate data table.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Building an order form or invoice where selecting a product from a drop-down menu automatically populates its price.
- Observed behavior
- A dynamic data-validation list needs to be linked to a lookup formula (XLOOKUP, INDEX/MATCH, or VLOOKUP) to accurately fetch matching prices based on the user's selection.
Ensure your product and price data is organized in clean, adjacent columns, preferably without blank rows, to make table creation and formula referencing easier.
Use Data Validation and XLOOKUP (Modern Excel)
Convert your source data into an Excel Table for dynamic updates, create a drop-down list, and use the robust XLOOKUP function to fetch prices.
Using an Excel Table ensures that any new products you add later will automatically appear in your drop-down list without needing to adjust the data validation range manually.
Highlight your product and price data range. Navigate to Insert > Table on the ribbon, check 'My table has headers', and click OK. Name the table 'ProductTable' for easier reference.
Go to Formulas > Define Name. Enter 'ProductList' as the Name. In the 'Refers to' box, select just the product column of your new table.
Select the target cell where you want the drop-down (e.g., C5). Go to Data > Data Validation. Under the 'Allow' drop-down, select 'List'. In the 'Source' box, type =ProductList and click OK.
Click the cell where the price should appear. Enter the formula: =XLOOKUP(C5,ProductTable[Product],ProductTable[Price],""). Press Enter. Now, selecting a product in C5 will instantly display its price.
Use VLOOKUP or INDEX/MATCH for Older Excel Versions
If you are using an older version of Excel that does not support XLOOKUP, use INDEX/MATCH or VLOOKUP combined with data validation.
Create Dynamic Drop-Downs and Lookups Easily in WPS Spreadsheet
WPS Spreadsheet fully supports advanced functions like XLOOKUP, VLOOKUP, and Data Validation, making it incredibly simple to build automated forms, invoices, and dynamic data tables without any compatibility issues.
- 1. Organize your data: Open your workbook in WPS Spreadsheet and ensure your product names and prices are listed in adjacent columns.
- 2. Create the Drop-Down: Select the target cell, navigate to the Data tab, and click 'Validation' to open the Data Validation dialog.
- 3. Configure the List: Select 'List' in the Allow drop-down box, click the Source field, and highlight your column of product names.
- 4. Apply Lookup Formula: In the adjacent cell, type your preferred lookup formula, such as =XLOOKUP() or =VLOOKUP(), referencing the drop-down cell to pull the corresponding price.
Frequently Asked Questions
Why is my drop-down list not updating when I add new products?
If your source data is just a standard range, the drop-down won't update automatically. Convert your source data to an Excel Table (Insert > Table) or use a dynamic Named Range to ensure new items are added to the list automatically.
Why does my VLOOKUP formula return an #N/A error after selecting a product?
The #N/A error usually means the selected product cannot be found in your source table. Check for accidental trailing spaces in your drop-down list or source data, and ensure your VLOOKUP formula is set to exact match by using FALSE as the last argument.
Can I place the product list data on a different worksheet?
Yes. The best way to do this is to create a Named Range for your product column on the source sheet. Then, in the Data Validation source box on your main sheet, simply type an equals sign followed by the name (e.g., =ProductList).
