Look Up a Product's Price
Your task: In F3, write a formula that finds the unit price of the SKU in F2.
Full instructions, scenario and goal
Scenario
A shop keeps its product list in a sheet. A colleague types a SKU code into F2 and needs the matching unit price in F3, without scrolling through the list. The SKU in F2 will change, and so may the order of the list.
Your task
- Look at the product list in A1:C9. Each SKU appears once.
- The SKU to look up is in F2.
- Select cell F3, next to the label "Unit price".
- Type a formula that finds the SKU from F2 in the list and returns its unit price. Press Enter.
- Select Check Answer to see whether your formula is correct.
You are done when
- F3 contains a formula that uses the SKU in F2, not a SKU you typed.
- It returns the unit price for the SKU, not the product name.
- It still works if the SKU in F2 changes or the list is reordered.
What you will practise
Retrieve a value from a table by searching for a key, so the answer follows the key in the cell instead of a fixed row.
The idea in 30 seconds
A lookup finds a row by a key and brings back a value from the same row, so you don't have to scan a list by eye. VLOOKUP searches the first column of a table for the key, then returns a value from the column you number. A final argument chooses an exact match (FALSE or 0) or an approximate match, and exact is what you want for codes and names.
Spreadsheet
Loading spreadsheet…
Keyboard shortcuts
- Arrow keys
- Move between cells
- Shift + Arrow keys, or drag with the mouse
- Select a range
- Ctrl + Arrow keys
- Jump to the edge of a block of data
- Type, or F2, or double-click
- Edit a cell (F2 switches arrow keys between moving and editing)
- Click or arrow keys inside a formula
- Insert a cell reference after =, +, (, comma and similar
- F4
- Cycle a reference through A1, $A$1, A$1, $A1
- Enter / Tab while editing
- Confirm and move down / right (Shift reverses)
- Esc
- Cancel the edit
- Ctrl + C / X / V
- Copy, cut and paste (formulas adjust like in Excel)
- Delete
- Clear the selected cells
- Ctrl + Z / Ctrl + Y
- Undo and redo
- Ctrl + Enter
- Check your answer
- Tab when not editing
- Leave the spreadsheet
Type your answer in cell F3. Click a cell and type. Enter confirms, Esc cancels, Ctrl+Enter checks your answer.Tap a highlighted cell to type in it, then tap the green tick to confirm. Scroll the sheet sideways if it is wider than your screen.
New here? How an exercise works
- Read the task. Highlighted cells are yours to fill in; the rest are locked.
- Select (or tap) a highlighted cell and type a formula. It starts with =, like =A1+B1.
- Press Check answer. Stuck? Reveal hints one at a time.
The full explanation: the idea behind this exercise
A lookup finds a row by a key and brings back a value from the same row, so you don't have to scan a list by eye. VLOOKUP searches the first column of a table for the key, then returns a value from the column you number. A final argument chooses an exact match (FALSE or 0) or an approximate match, and exact is what you want for codes and names.
Newer Excel offers XLOOKUP, which takes a search range and a return range separately, and the INDEX and MATCH pair, which does the same in two steps. All three follow the key wherever it sits, so the answer stays right when the list is re-sorted or when new rows are added, which a typed row number never does.
Skills you will practise
- VLOOKUP
- XLOOKUP
- INDEX and MATCH
- Exact match
Topic guide: Excel Lookup Functions Practice: VLOOKUP, XLOOKUP, INDEX MATCH
Common mistakes to avoid
- Returning the wrong column
- The column number counts from the first column of the table you give VLOOKUP. Column 2 here is the product name; the price is column 3.
- A table that does not start at the key column
- VLOOKUP searches the first column of its table. If the table starts at B, it searches product names and finds nothing (#N/A).
- Typing the SKU or the price
- Typing "SKU-1006" or the price into the formula ignores F2, so it is wrong as soon as F2 changes.