VLOOKUP inside a calculation
Look the price up and multiply by the quantity in the same formula.
Task:
The order form has quantities but no prices. In C2:C6 give each line's total — quantity times the unit price from the price list in E2:F6 — in one formula per line.
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 | E | F | |
|---|---|---|---|---|---|---|
| 1 | Item | Qty | Line total | Item | Unit price | |
| 2 | Notepad | 12 | Stapler | 7.5 | ||
| 3 | Stapler | 2 | Notepad | 1.2 | ||
| 4 | Toner | 3 | Ring binder | 2.8 | ||
| 5 | Ring binder | 10 | Toner | 46 | ||
| 6 | Notepad | 30 | Paper clips | 0.9 |
What this exercise teachesMay contain the answer
A lookup is just a value once it returns, so it can sit inside arithmetic. Doing it in one cell avoids a helper column of prices that someone later overtypes — the total always reads the current list.