How to Automatically Populate Quotation Prices from a Drop-down in Excel
Question details
The user wants to use a drop-down menu in a quotation template to automatically retrieve and populate the corresponding product's price, description, and quantity.

- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Building a dynamic quotation template that minimizes manual data entry by pulling product details from a master list when an item is selected.
- Observed behavior
- Requires a formula-based solution to link a data validation drop-down selection to a product database for automatic data retrieval.
Ensure your master product list is organized logically with clear column headers (e.g., Item, Price, Description) before building your drop-down lists and lookup formulas.
Use XLOOKUP to Auto-Populate Product Details
The XLOOKUP function is the most efficient and robust method to automatically fetch prices and descriptions based on a drop-down selection.
XLOOKUP is a powerful function that searches a range or array for a match and returns the corresponding item from a second range or array. By combining this with structured Excel tables, your quotation template will automatically update when new products are added without needing to manually adjust formula ranges.
Select your entire product database range, press Ctrl+T on your keyboard, check 'My table has headers', and name the table 'Products' in the Table Design tab.
In your quotation template sheet, click the cell where you want the product name to appear. Go to the 'Data' tab on the ribbon, click 'Data Validation', choose 'List' under the Allow settings, and select the 'Item' column of your 'Products' table as the source.
Select the cell where the price should appear. Assuming your drop-down is in cell A2, enter the formula: =XLOOKUP(A2, Products[Item], Products[Price], ""). Press Enter to calculate the price.
Repeat the XLOOKUP formula for other fields like Description or Quantity by changing the return array. For example, to pull the description, use: =XLOOKUP(A2, Products[Item], Products[Description], "").

Create Dynamic Quotation Templates Easily in WPS Spreadsheet
WPS Spreadsheet fully supports advanced array functions like XLOOKUP, Data Validation, and Structured Tables, making it incredibly simple to build automated, professional, and error-free quotation templates.
- 1. Download and Install: Download WPS Office and open WPS Spreadsheet. You can create a new blank workbook or open your existing .xlsx quotation template.
- 2. Set Up Your Product Table: Input your product database and press Ctrl+T to convert it into a structured table, ensuring dynamic data referencing.
- 3. Create Drop-downs: Navigate to the Data tab and use Data Validation to create intuitive drop-down lists linked to your product table.
- 4. Automate with XLOOKUP: Type your =XLOOKUP formulas to automate pricing and descriptions, then save your work seamlessly in standard formats.

Frequently Asked Questions
Why does my XLOOKUP formula return an #N/A error?
This error occurs if the lookup value from your drop-down doesn't exactly match any item in your product list. Ensure there are no extra trailing spaces in your product names. You can also utilize the 'If_not_found' parameter in XLOOKUP (such as adding "" at the end) to display a blank cell instead of the error.
Can I use VLOOKUP instead of XLOOKUP to auto-populate prices?
Yes, VLOOKUP can be used if your lookup value (the Product Item) is in the very first column of your source table. However, XLOOKUP is highly recommended because it is more flexible and won't break if you insert or delete columns in your product list.
How do I make the drop-down list update automatically when I add new products?
By converting your source product list into a structured table (using the Ctrl+T shortcut) and naming it, the drop-down list (Data Validation) and your XLOOKUP formulas will automatically expand to include any new rows you add to the bottom of the table.
Can I pull product data from a different worksheet?
Absolutely. You can reference ranges across different sheets by navigating to the desired sheet while constructing your XLOOKUP formula. The formula bar will automatically include the sheet name followed by an exclamation mark, such as Sheet2!A2:A100.




