Buy more, pay less per unit. A quantity-break table plus an approximate-match VLOOKUP finds the right unit price (or discount) for any order size — the bigger the order, the better the rate.
The example
An order of 60 units hits the “50+” price of $8.
| A | B | C | |
|---|---|---|---|
| 1 | Min qty | Unit price | |
| 2 | 1 | $10 | |
| 3 | 20 | $9 | |
| 4 | 50 | $8 | |
| 5 | 60 units → total | $480 |
The formula
Find the unit price for the order size:
How it works
Approximate-match VLOOKUP reads the price break:
- Build a break table: the minimum quantity for each price in the left column (1, 20, 50…), sorted ascending, with the unit price beside it.
VLOOKUP(qty, table, 2, TRUE)finds the largest break the order meets and returns that unit price.- Multiply by the order quantity for the line total.
- Prefer a discount percent? Put the discount in the table instead and compute
=qty * listPrice * (1 - discount).
Show the savings: compare against the list (tier-1) price — =qty*listPrice - qty*tierPrice tells the customer how much the volume break saved them, a nice line on the quote.
Try it: interactive demo
Enter an order quantity.
Variations
Discount percent table
Store discounts, not prices:
Show the savings
Versus list price:
Free shipping over N
Threshold perk:
Pitfalls & errors
Sort breaks ascending. Approximate match needs the min-quantity column low-to-high, or it returns the wrong tier.
First row must start at the minimum. Begin the table at quantity 1 (or 0) so small orders still match a price instead of erroring.
Whole-order pricing. This charges one rate for all units. If only units above each break get the discount, you need a bracket-style calc.
Practice workbook
Frequently asked questions
How do I apply quantity discounts in Excel?
Can I store discount percents instead of prices?
Why does a small order return an error?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class