Flat-rate pricing means the same job always costs the same. Keep a book-rate table and let VLOOKUP pull the price for any job — consistent quotes, no mental math.
Pick "Fridge not cooling" and the table returns its flat $180 — the same price every technician quotes.
The example
A job list on the left looks up its flat price from the rate table on the right.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Job | Flat fee | Service type | Book rate | |
| 2 | Fridge not cooling | $180 | Fridge not cooling | $180 | |
| 3 | Dryer no heat | $160 | Washer won't drain | $150 | |
| 4 | Oven won't ignite | $140 | Dryer no heat | $160 | |
| 5 | Washer won't drain | $150 | Oven won't ignite | $140 |
The formula
One lookup prices any job on the list:
How it works
VLOOKUP matches the job to the table and hands back the price:
A2is the job to price — the value VLOOKUP searches for.$D$5:$E$8is the rate table, locked with dollar signs so it does not shift as you copy down.2returns the second column (the price);FALSEdemands an exact match so a typo errors instead of guessing.
Add new services by extending the table; every job line picks them up automatically.
Try it: interactive demo
Pick a job and see its flat-rate price.
Variations
Default for unknown jobs
Catch jobs that are not in the table and fall back to a diagnostic fee.
Pitfalls & errors
Without FALSE, VLOOKUP does an approximate match and can return the wrong job's price. Always pass FALSE for flat-rate menus.
Lock the table range with $ ($D$5:$E$8) so it stays put when you copy the formula down the job list.
Practice workbook
Frequently asked questions
Why FALSE at the end?
What if the job is not in the table?
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