Function Problems

How to Automatically Populate Quotation Prices from a Drop-down in Excel

Nimra MalikNimra Malik Nov 11, 2026 871 views

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.

How to Automatically Populate Quotation Prices from a Product List
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.
Before you start

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.

Solution 1Recommended

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.

1
Format the Product List as a Table

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.

2
Create a Drop-down List

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.

3
Enter the XLOOKUP Formula for Price

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.

4
Set Up Formulas for Other Columns

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], "").

Use XLOOKUP to Auto-Populate Product Details
Handling Blank Selections: The empty quotes "" at the end of the XLOOKUP formula tell the program to return a blank cell instead of an ugly #N/A error if no product is selected in the drop-down.
Efficient Quotation Automation

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. 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. 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. 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. 4. Automate with XLOOKUP: Type your =XLOOKUP formulas to automate pricing and descriptions, then save your work seamlessly in standard formats.
Effortlessly insert Data Validation drop-downs and advanced lookup formulas100% compatibility with Microsoft Excel formulas and .xlsx document formatsFree to use with a lightweight, intuitive, and tabbed user interfaceAccess to a rich library of built-in professional invoice and quotation templates
microsoft office alternative - wps office

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.