Basic VLOOKUP Function
Pull a price out of a second table without going to find it yourself.
Task:
The order sheet has parts and quantities; the prices live in a separate list someone else maintains. Fill column C with each part's price, read from that list, so it stays right when they update it.
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 | Part | Quantity | Unit price | Price list | ||
| 2 | HX-200 hinge | 10 | Door closer | 8.4 | ||
| 3 | Wall bracket | 15 | M6 bolt (100pk) | 3.2 | ||
| 4 | M6 bolt (100pk) | 8 | HX-200 hinge | 2.5 | ||
| 5 | Wall bracket | 1.8 |
What this exercise teachesMay contain the answer
The price list is in a different order from the order sheet, which is the normal state of two lists maintained by two people. That is exactly why the lookup earns its place: reading across from the same row would have given you the door closer's price for a hinge, and nothing about the result would have looked wrong.