Typing the price again and again
A shop till list holds only item codes. The price is on a separate price list. Typing every price by hand for 300 orders would take all day and bring in mistakes.
A lookup finds the price for you, using the code.
Key words
| Word | What it means |
|---|---|
| Lookup | finding a value in one table by searching for a key in another place |
| VLOOKUP | searches down the first column and reads across from the row it finds |
| HLOOKUP | searches along the first row and reads down from the column it finds |
| Exact match | the search value has to be identical to the one found |
| Error value | a message such as #N/A that a formula shows when it fails |
| IFERROR | shows a fallback of your choice when a formula gives an error |
| Formula | a calculation typed into a cell, starting with = |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can use VLOOKUP, HLOOKUP and IFERROR to find values, and say when each is used.
Starter
Have a go
Starter
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
No spreadsheet here
Important
This platform has no spreadsheet program. Here the cells are a table and the formula is shown as text. You do the same job with code. On site you use a real spreadsheet program.
The price list as cells
| Row | A code | B item | C price in pence |
|---|---|---|---|
| 1 | C1 | Cola | 120 |
| 2 | A1 | Apple | 40 |
| 3 | K1 | Cookie | 110 |
| 4 | W1 | Water | 80 |
The cells students fill in are left blank in this preview.
VLOOKUP, part by part
| Part | In `=VLOOKUP("K1", A1:C4, 3, FALSE)` | Meaning |
|---|---|---|
| Search value | `"K1"` | What to look for |
| Table | `A1:C4` | Search the first column of this block |
| Column number | `3` | Read the third column of the row found |
| Match type | `FALSE` | Exact match only |
| Answer | 110 | The price of K1 |
The cells students fill in are left blank in this preview.
Try it: the same lookup in Python
prices = [["C1", "Cola", 120], ["A1", "Apple", 40], ["K1", "Cookie", 110]]
wanted = "K1"
answer = "#N/A"
for row in prices:
if row[0] == wanted:
answer = row[2]
print(answer)
# Change wanted to "Z9". What is printed?
HLOOKUP turns it on its side
Use HLOOKUP when the headings run along the top, not down the side. It searches the first row and reads down.
=HLOOKUP("K1", A1:C3, 3, FALSE) on a sideways list gives 110 too. Choose V for a list that runs down and H for a list that runs across.