Function Problems

How to Create an Excel Order Form with Automatic Price Lookup

Bushra Parveen Oct 23, 2026 868 views

Question details

The user wants to create an Excel order form that automatically retrieves product prices from a separate price list based on the item selected.

How to Create an Excel Order Form with Automatic Price Lookup

Product: Excel
Device & OS: not provided
Scenario: Setting up a sales invoice, inventory sheet, or order form where product prices need to populate automatically to save time and reduce manual entry errors.
Observed behavior: The form needs to feature a drop-down menu to restrict product selection to valid names, and a lookup formula that fetches the corresponding unit price for the chosen item.

Before you start

Prepare a complete and accurate reference sheet containing all your unique product names in one column and their corresponding unit prices in the next.

Solution 1Recommended

Use Data Validation and VLOOKUP for Automatic Pricing

Combine a drop-down list to select items and a VLOOKUP formula to fetch the exact price from your reference sheet.

By restricting user input with Data Validation, you ensure that product names are spelled exactly as they appear in your database. The VLOOKUP function then seamlessly searches for that exact match and retrieves the corresponding price.

Create the Price List Table

Open your workbook and create a new worksheet named 'PriceList'. Enter your unique product names in column A and their prices in column B.

Insert a Drop-Down List

Navigate to your order form sheet. Click the cell where you want to select an item (e.g., A2). Go to the 'Data' tab on the ribbon, click 'Data Validation', choose 'List' under the Allow drop-down, and select the range of product names from your 'PriceList' sheet.

Apply the VLOOKUP Formula

In the adjacent price cell (e.g., B2), type the formula =VLOOKUP(A2,PriceList!A:B,2,FALSE) and press Enter. This tells Excel to look for the item from A2 in the price list and return the value from the second column.

Copy the Formula Down

Select cell B2, click the small square at the bottom-right corner of the cell (fill handle), and drag it down to apply the lookup formula to all rows in your order form.

Use Data Validation and VLOOKUP for Automatic Pricing

Hiding #N/A Errors: If no item is selected in the drop-down list, the VLOOKUP formula will return an #N/A error. You can hide this by wrapping your formula in IFERROR, like this: =IFERROR(VLOOKUP(A2,PriceList!A:B,2,FALSE), "").

Efficient Spreadsheet Software

Automate Your Forms Easily with WPS Spreadsheet

WPS Spreadsheet offers powerful data management tools like VLOOKUP and easy-to-use Data Validation to help you create error-free automated order forms effortlessly.

1. Set up your sheets: Open WPS Spreadsheet, create a 'PriceList' sheet with your inventory data, and an 'Order Form' sheet for data entry.
2. Create drop-downs: Select the product column on your order form, click the Data tab, and choose Data Validation to bind your product names.
3. Automate pricing: Enter the VLOOKUP formula in the price column, wrap it in IFERROR if needed, and drag the fill handle down to complete your form.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx).Intuitive Data Validation interface for creating drop-down menus in seconds.High-speed calculation engine for processing complex lookup functions.Access to thousands of free, customizable invoice and order form templates.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VLOOKUP formula return the wrong price?

This usually happens when the range lookup argument is set incorrectly. Ensure the last argument in your VLOOKUP formula is set to FALSE (or 0) to force an exact match, e.g., =VLOOKUP(A2,PriceList!A:B,2,FALSE).

Can I calculate the total price automatically based on quantity?

Yes. Add a 'Quantity' column next to your unit price. In a new 'Total Price' column, enter a formula to multiply the two cells (e.g., =B2*C2). As the VLOOKUP function fetches the unit price, the total will update dynamically when a quantity is entered.

Can I use XLOOKUP instead of VLOOKUP for this task?

If your version of Excel or WPS Spreadsheet supports it, XLOOKUP is an excellent alternative. You can use =XLOOKUP(A2, PriceList!A:A, PriceList!B:B) which is more flexible, handles errors better, and doesn't require column index numbers.

Excel & Data Problems