Look up a consultant's billing rate from a two-way rate grid with INDEX and MATCH
A rate that depends on two things at once, not one, lives in a grid rather than a list — and INDEX can land on any cell in that grid once two MATCH calls work out which row and which column to use.
Task:
You handle billing for a management consulting firm. What a client gets charged per hour depends on two things together — the consultant's level and the client's service tier — so the rate card is laid out as a grid, one row per level and one column per tier, rather than a single list a plain VLOOKUP could read. For each engagement in A2:B5, use INDEX and MATCH in column C to pull the rate that matches both the level in column A and the tier in column B from the grid in E1:H5. Fill in rows 2 through 5.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve 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.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Level | Tier | Billing Rate ($/hr) | Level | Standard | Premium | Enterprise | |
| 2 | Senior Associate | Premium | Associate | 150 | 165 | 180 | ||
| 3 | Manager | Enterprise | Senior Associate | 195 | 215 | 235 | ||
| 4 | Associate | Standard | Manager | 250 | 275 | 300 | ||
| 5 | Partner | Premium | Partner | 320 | 350 | 385 |
What this exercise teachesMay contain the answer
INDEX($F$2:$H$5,row,column) only knows how to pull one cell out of the rate grid once it has been told exactly which row and which column, and typing those positions in by hand would break the moment the grid got reordered. The two MATCH calls work those positions out instead: for Senior Associate and Premium in row 2, MATCH(A2,$E$2:$E$5,0) finds that "Senior Associate" sits second among the grid's row labels, and MATCH(B2,$F$1:$H$1,0) finds that "Premium" sits second among its column headers — so INDEX returns the cell at the grid's second row and second column, 215, not the 195 a Standard-tier Senior Associate would be billed at, or the 235 an Enterprise one would. The same pair of lookups in row 3 lands on Manager's third row and third column, 300, because each MATCH call only cares about the value it's given, never about what the row above asked for.