ICT · Spreadsheets
Why does my VLOOKUP return #N/A when the value is clearly in the table?
Four causes, and in a practical exam it is nearly always the fourth. Work through them in order rather than retyping the formula and hoping.
1. The lookup value is not in the first column
VLOOKUP only searches the leftmost column of the range you give it. If your product codes sit in column C and your range starts at column A, it is searching column A and will never find them.
Fix: start the range at the column containing the lookup value.
=VLOOKUP(A2,C2:F50,2,FALSE) searches column C, not column A.
2. The range is not locked, so it moves when you fill down
This one is cruel because the first row works perfectly and the errors start three rows down.
=VLOOKUP(A2,C2:F50,2,FALSE) filled down becomes C3:F51, then C4:F52. The
top of your table slides out of range, and any lookup that needed those rows
fails.
Fix: absolute references. =VLOOKUP(A2,$C$2:$F$50,2,FALSE). The dollar signs
pin the range so it does not shift. In a practical paper, forgetting these is
probably the single most common way to lose marks on a lookup task.
3. The last argument is missing or TRUE
Leaving the fourth argument off gives approximate matching, which returns the nearest smaller value and requires the table to be sorted. On unsorted data it returns confident nonsense rather than an error, which is worse than #N/A because you will not notice.
Fix: type FALSE (or 0) for an exact match. Get in the habit of always typing
it, every time, even when you think approximate is fine.
4. The values look identical but are not
This is the one that wastes the most time in an exam, because the cells look right on screen.
The usual culprits:
- A trailing space.
"A102 "is not"A102". Imported or pasted data is full of these. - A number stored as text.
1024typed into a text-formatted cell will not match1024stored as a number. The tell is alignment: text sits left, numbers sit right. If your lookup column is left-aligned and your table column is right-aligned, that is your answer. - A non-breaking space from copied web data, which looks exactly like a normal space and is not.
Diagnose it in one step. In a spare cell, type =A2=C2 comparing the two cells
you believe are equal. If it returns FALSE, they are not equal, and now you know
the problem is data rather than formula.
Fix with =TRIM(A2) to strip spaces, or =VALUE(A2) to convert text to a
number. In a practical paper, clean the data in a helper column rather than
retyping it by hand.
Wrapping the error
Once the formula is right, a question may ask you to show something friendlier
than #N/A for codes that genuinely are not in the table:
=IFERROR(VLOOKUP(A2,$C$2:$F$50,2,FALSE),"Not found")
Use this only after the formula works. Wrapping a broken VLOOKUP in IFERROR hides the bug and turns a diagnosable error into silent wrong output, and examiners can tell the difference.
Why this matters more in ICT than it looks
The practical papers are marked on evidence, and a spreadsheet full of #N/A
loses the marks for that task even if the method was almost right. Knowing the
four causes in order means you spend ninety seconds diagnosing rather than ten
minutes retyping, and in a paper where time is the binding constraint that gap
is worth more than the task itself.
Working from a past paper? Mark your attempt against the published mark scheme before reading an answer like this one. Finding out where you lost the marks is worth more than seeing a correct solution, because the correct solution rarely tells you what you did instead.
Related questions
Ordered by how close they are to this one: same topic first, then the same subject.
I name the technique and still lose marks. What does 'analyse the effect' actually want?
It wants the specific thing that this word does to this reader in this sentence. Naming the device identifies the tool. The marks are for what the tool was used to…
How do I work out concentration when the question gives me a mass and a volume?
Always through moles. Mass and volume never connect directly, and trying to make them is the single most common reason these questions go wrong.
How do I show a quadratic has two distinct roots without solving it?
Use the discriminant, and state the conclusion in the form the mark scheme wants. Most students get the arithmetic right here and lose the mark on the final line.
A 1200 kg car has a driving force of 4000 N and 1600 N of resistance. What is its acceleration?
2.0 m/s². The whole question is one idea: find the resultant force first, then apply F = ma. Most lost marks here come from skipping the first word.
Why does my trace table always go wrong on the last iteration?
Because you are almost certainly checking the condition at the wrong moment, and the last row is the only place that difference shows up.
How do I write a negative number in two's complement without getting confused?
There is a two-step method that always works, and most of the confusion comes from students half-remembering a third step that does not exist.