Lookup FunctionsIntermediate

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.

Interactive Spreadsheet

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

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.

6 rows × 7 columns5 cells you fill in
ABCDEFG
1CustomerPlanSeatsMonthly billPlanPrice per seat
2Brightline MediaGrowth12Starter8
3Kestrel AnalyticsEnterprise6Growth15
4Foundry CoworkingStarter40Enterprise27
5Petrel LogisticsGrowth22
6Total
What this exercise teachesMay contain 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.