VLOOKUP with MATCH choosing the column
Let the column header decide which column comes back.
Task:
The rate card has one column per size. In C10:C12 return each order's price, finding the product's row by name and the size's column by its header — so a reordered rate card does not break the formula.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of VLOOKUP
0/4The same formula in the other shapes it takes at work.
- Basic VLOOKUP Function
Pull a price out of a second table without going to find it yourself.
CoreIntermediate - VLOOKUP approximate match for bands
Find which band a number falls into, with the table sorted by each band's starting point.
Intermediate - VLOOKUP inside a calculation
Look the price up and multiply by the quantity in the same formula.
Intermediate - VLOOKUP with MATCH choosing the column
Let the column header decide which column comes back.
Advanced
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 | |
|---|---|---|---|---|
| 1 | Product | Small | Medium | Large |
| 2 | Latte | 2.9 | 3.4 | 3.9 |
| 3 | Mocha | 3.2 | 3.7 | 4.2 |
| 4 | Tea | 1.8 | 2.1 | 2.4 |
| 5 | Flat white | 3 | 3.5 | 4 |
| 6 | ||||
| 7 | ||||
| 8 | ||||
| 9 | Order | Size | Price | |
| 10 | Mocha | Large | ||
| 11 | Tea | Small | ||
| 12 | Latte | Medium |
What this exercise teachesMay contain the answer
A typed column number is the most fragile part of a VLOOKUP: insert a column into the table and every formula returns the wrong field without an error. Matching the header makes the formula look the column up by name, so it keeps working however the table is rearranged.