Repair shops mark up parts by a sliding scale — cheap parts get a higher percentage, expensive ones less. A markup matrix (approximate-match LOOKUP) applies the right rate to any part cost automatically.
The example
$40 part in the 2x band.
| A | B | |
|---|---|---|
| 1 | Cost band | Markup |
| 2 | $0–$50 | 100% |
| 3 | $50–$200 | 60% |
The formula
The formula:
How it works
How it works:
- Build a matrix: ascending cost breakpoints and the markup percent for each band.
LOOKUP(cost, breaks, rates)finds the markup for the part’s cost band.- Apply it:
cost × (1 + markup)— the customer sell price. - Lower-cost parts carry higher markup to cover handling; big-ticket parts a slimmer rate.
Sliding markup protects margin on small parts. A flat 30% on a $3 clip earns 90 cents — not worth the handling. A matrix charges 150% on tiny parts and tapers to 25% on a $900 component, so every part is profitable without overpricing the expensive ones. The same approximate-match LOOKUP pattern drives labor-rate and discount tiers too.
Try it: interactive demo
Part cost (matrix: <$50=100%, <$200=60%, else 35%).
Variations
Markup amount
Profit on the part:
VLOOKUP version
From a table:
Flat-markup compare
Single rate:
Pitfalls & errors
Sort breakpoints ascending. Approximate-match LOOKUP/VLOOKUP needs them in order.
Start at 0. Cover the lowest band so cheap parts still match.
Markup vs margin. A 100% markup is a 50% margin — don’t confuse them.
Practice workbook
Frequently asked questions
How do I build a parts markup matrix in Excel?
Why use a sliding markup?
Can I use VLOOKUP instead?
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