Bill each customer for the seats they're on
VLOOKUP fetches a per-seat price, then it's just one multiplication to turn that into a bill.
Task:
You handle billing for a small SaaS company that resells seat-based licenses. Four customers are signed up on different plans, and the price per seat for each plan lives in a reference table to the right. Work out each customer's monthly bill in column D, then total the month's billing in D6.
Learning Objectives:
- Look up a value by exact match with VLOOKUP's fourth argument set to FALSE
- Turn a looked-up rate into a total with a single multiplication
- Lock a reference table's range with absolute references so it survives being filled down
Solve without hints for +5 XP
Interactive Spreadsheet
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Customer | Plan | Seats | Monthly bill | Plan | Price per seat | |
| 2 | Brightline Media | Growth | 12 | Starter | 8 | ||
| 3 | Kestrel Analytics | Enterprise | 6 | Growth | 15 | ||
| 4 | Foundry Coworking | Starter | 40 | Enterprise | 27 | ||
| 5 | Petrel Logistics | Growth | 22 | ||||
| 6 | Total |
What this exercise teaches (contains the answer)
VLOOKUP(B2,$F$2:$G$4,2,FALSE) walks down the plan names in F2:F4 until it matches Growth, then returns the value one column across in G — 15, this table's price per seat for that tier. FALSE forces an exact match, which matters here because the plan names aren't sorted and a partial or approximate hit would silently return the wrong tier's price. Multiplying that by C2 turns a per-seat rate into a bill for however many seats this customer actually has, and the absolute references on the table's range keep it pinned in place as the formula fills down to Petrel Logistics three rows later — without them, row 5's lookup range would have drifted three rows down and started searching a table that no longer starts at F2.