A home cook with four knives and a restaurant with twenty-five should not pay the same rate. Build a small tier table, let VLOOKUP find the right per-knife price for the count, and multiply.
Twelve knives fall in the 10-and-up bracket at $5 each — $60. Four knives sit at the $7 rate for $28.
The example
Left, the jobs with a knife count; right, the tier table of minimum quantity and per-knife rate.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Customer | Knives | Total | Qty from | $/knife | |
| 2 | Chef set | 12 | 60 | 1 | 7 | |
| 3 | Home | 4 | 28 | 5 | 6 | |
| 4 | Restaurant | 25 | 100 | 10 | 5 | |
| 5 | 20 | 4 |
The formula
The TRUE flag makes VLOOKUP land in the right bracket instead of demanding an exact match:
How it works
Approximate-match lookup, then a multiply:
VLOOKUP(B2,$E$2:$F$5,2,TRUE)scans the sorted quantity brackets and returns the rate for the largest threshold at or below the count.- The
TRUE(approximate) flag is what lets 12 match the 10 bracket — you do not need a row for every possible count. B2*...multiplies that per-knife rate by how many knives are on the ticket.- Keep the tier table sorted ascending by quantity, or approximate match returns the wrong row.
Add or reprice a tier by editing the little table — every ticket repricing follows without touching a formula.
Try it: interactive demo
Enter the knife count; the tiers are 1+ $7, 5+ $6, 10+ $5, 20+ $4.
Variations
Serrated surcharge
Add a flat per-serrated-blade fee on top of the volume price.
Flat shop minimum
Never let a tiny ticket fall below a floor.
Pitfalls & errors
Approximate-match VLOOKUP needs the tier table sorted ascending by the lookup column. An out-of-order quantity list returns whatever row it stops on — usually the wrong rate.
Start the table at 1, not 5, so any count of one or more always finds a bracket. A count below the first threshold returns #N/A.
Practice workbook
Frequently asked questions
Why TRUE instead of FALSE in the VLOOKUP?
How do I add a new tier?
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