Function Problems

How to Calculate Unit Price from Order Quantity in Excel

John Wilson Oct 22, 2026 869 views

Question details

The user needs to find a way to automatically retrieve the correct unit price based on different order quantity tiers or volume discounts in Excel.

How to Calculate Unit Price Based on Order Quantity in Excel

Product: Excel
Device & OS: not provided
Scenario: Setting up a dynamic pricing table where the unit cost decreases or changes based on the volume of items ordered.
Observed behavior: Retrieving the correct tiered pricing dynamically based on the input quantity using lookup functions.

Before you start

Ensure your tiered pricing table is sorted in ascending order based on the quantity thresholds, starting from the minimum quantity (usually zero).

Solution 1Recommended

Use VLOOKUP with Approximate Match for Tiered Pricing

The VLOOKUP function with the TRUE argument allows you to find the largest quantity threshold that is less than or equal to the ordered amount.

When dealing with quantity breaks (e.g., 1-9 items cost $5, 10-24 items cost $4.50), an approximate match is the most efficient method to pull the correct price without listing out every single possible number.

Create a pricing table

In one column (e.g., D2:D5), enter the minimum quantity for each tier in ascending order (0, 10, 25, 50). In the next column (e.g., E2:E5), enter the corresponding unit price.

Input the target quantity

Enter your specific order quantity in a separate cell, such as A2.

Apply the VLOOKUP formula

Select the cell where you want the calculated unit price to appear, input the formula =VLOOKUP(A2, $D$2:$E$5, 2, TRUE), and press Enter.

Absolute References: Remember to lock your table array range using absolute references (like $D$2:$E$5) if you plan to copy the formula down to multiple rows.

Solution 2

Use XLOOKUP for Flexible Quantity Match

XLOOKUP is a newer, more robust function that provides specific match mode settings and does not require the lookup column to be sorted if structured properly.

Calculate Tiered Pricing Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced lookup functions like VLOOKUP and XLOOKUP, allowing you to build dynamic and responsive pricing models seamlessly.

1. Open your workbook: Launch WPS Spreadsheet and open the file where you manage orders.
2. Set up your threshold table: Create a 2-column table with your minimum quantity limits and corresponding unit prices in ascending order.
3. Apply the lookup function: Click into your target cell, type =VLOOKUP(A2, D2:E5, 2, TRUE), and hit Enter to auto-calculate your unit prices.

100% compatible with Microsoft Excel formulas (.xlsx format)Built-in function assistance for correctly typing VLOOKUP argumentsLightweight application that runs smoothly on Windows, Mac, and LinuxFree to use with a familiar, easy-to-navigate tabbed interface

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VLOOKUP returning the wrong unit price for certain quantities?

This usually happens if your lookup table is not sorted in ascending order. When using VLOOKUP with an approximate match (TRUE), the first column of your table must be sorted strictly from smallest to largest to work correctly.

How do I calculate the total order cost after finding the unit price?

Simply multiply the result of your lookup formula by the order quantity. For example, use the formula =A2 * VLOOKUP(A2, D2:E5, 2, TRUE) where A2 is your order quantity.

Can I use an IF statement instead of VLOOKUP for quantity breaks?

Yes, you can use nested IF or IFS functions (e.g., =IF(A2>=50, 3.5, IF(A2>=25, 4, ...))), but VLOOKUP or XLOOKUP is highly recommended because it is easier to read, update, and manage, especially when you have many pricing tiers.

Excel & Data Problems