A lookup takes a value, finds it in the first column of a table, and returns something from the same row. It is only reliable when its assumptions are true, so a good answer states them. The table and codes below are invented.
What does a lookup assume?
For =VLOOKUP(value, table, column number, match type) you are assuming:
- The value you search for is in the first column of the table. The function never looks to the left.
- The match type is stated.
FALSEmeans exact match.TRUEmeans approximate match, which needs a sorted first column. - Codes are unique. Two rows with the same code would return only one of them.
- The data types agree. The code
P02as text must match text, and a number must match a number. - The table range is locked with dollar signs.
- The column number counts from the table’s own first column, not from column A of the sheet.
Worked example
An invented product table in F2:H4:
| F | G | H | |
|---|---|---|---|
| 1 | Code | Item | Price |
| 2 | P01 | Pen | 1.50 |
| 3 | P02 | Ruler | 2.25 |
| 4 | P03 | Notebook | 3.20 |
Cell A2 holds the code P03 and B2 holds a quantity of 6.
Step 1, price: in C2 type =VLOOKUP(A2,$F$2:$H$4,3,FALSE). The code is found in row 4, and column 3 of the table is Price, so it returns 3.20.
Step 2, total: in D2 type =B2*C2. Check: 6 × 3.20 = 19.20.
Step 3, copy down. A2 becomes A3, but $F$2:$H$4 stays locked, so every row searches the same table.
For banded values, use a sorted table and TRUE. With bands 0 Fail, 50 Pass, 70 Merit in a two-column table, a mark of 62 returns Pass, because 50 is the largest start that is not above 62.
The mistake to watch for
Mistaken formula:
=VLOOKUP(A2,$F$2:$H$4,2,FALSE)It runs without an error, but returns Notebook, not 3.20. Column 2 is the item name.
The column number counts across the table: Code is 1, Item is 2, Price is 3. The fix is changing 2 to 3. Plausible wrong answers are the dangerous kind, so always compare the result with the table by eye.
Also beware of leaving off the match type: with an unsorted first column the approximate default can return an unrelated row.
Check yourself
Try these, then open each answer.
1. What does =VLOOKUP("P01",$F$2:$H$4,2,FALSE) return?
Show answer
Code P01 is in row 2, and column 2 is Item. It returns Pen.
2. What does =VLOOKUP("P04",$F$2:$H$4,3,FALSE) return, and why?
Show answer
#N/A. There is no P04 in the first column, and exact match does not guess.
3. Using the band table (0 Fail, 50 Pass, 70 Merit) with approximate match, what do 49, 50 and 70 return?
Show answer
49 is Fail, 50 is Pass and 70 is Merit. Each returns the band whose start is the largest value not above it.
Where this leads next
Lookups are often copied, so the next skill is diagnosing a formula copied into the wrong range. Review absolute references if the table range slid. The ICT practical task and evidence checker is a quick review for a finished task.
If you want an experienced teacher to check your lookup assumptions on your own practice sheets, see online one-to-one ICT tuition.