VLOOKUP is the function people Google most when it breaks — and it breaks in the same five ways, over and over. The formula looks right, the value is right there in the table, and Excel still says #N/A. This post walks through the five causes in the order you should check them, because the fix is usually a thirty-second change once you know where to look.
Related: How to Use VLOOKUP in Excel
What VLOOKUP Fixes does
Most VLOOKUP failures are not formula errors — they are data mismatches. A trailing space, a number stored as text, or a missing FALSE argument makes two values that look identical fail to match. Each cause below has a quick test that tells you whether it is the culprit, so you can stop guessing and fix the right thing.
The syntax
=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)
- lookup_value — the value to find — it must match the table exactly, spaces and type included.
- table_array — the table to search. The lookup column must be first.
- col_index_num — which column to return, counting from the left starting at 1.
- FALSE — exact match. Leaving this out is the number-one cause of wrong answers.
Step-by-step example
Work through these five checks in order whenever VLOOKUP fails.
- Check the last argument first. If your formula has no fourth argument, or it says TRUE, add
,FALSE— without it VLOOKUP returns the closest value below yours and needs a sorted table. - Test for extra spaces: in a spare cell type
=A2=B2comparing your lookup value with the table value. If it says FALSE, one of them has a space. Clean both sides with=TRIM(). - Test for numbers stored as text: type
=ISNUMBER(A2)on both sides. If one is TRUE and the other is FALSE, they will never match. Convert withvalue*1or Text to Columns. - Count the columns again. The column number counts from the left of your table range, not from column A of the sheet — an easy mistake when the table starts at column C.
- Check the lookup column is first. If the code is in column B and the price in column A, VLOOKUP cannot do it at all — switch to INDEX-MATCH or XLOOKUP.
Worked example
| Lookup value | Table value | Match? |
|---|---|---|
| A1003 | A1003 | No — trailing space |
| 120 | “120” (text) | No — text vs number |
| A1003 | A1003 | Yes |
The first two rows look identical on screen but fail the match test for invisible reasons. That is why the =A2=B2 test matters: it shows you what Excel sees, not what you see.
Common errors
#N/A even though the value is visible. Run the two tests above: spaces with TRIM, and number-versus-text with ISNUMBER. Nine times out of ten, one of them is the answer.
Wrong answer, no error at all. The fourth argument is missing or TRUE. Add FALSE. This is the silent failure — everything looks fine and the numbers are wrong.
#REF! after deleting a column. The column number now points past the table. VLOOKUP does not adjust when columns are deleted, which is another reason INDEX-MATCH is safer for tables that change.
It worked yesterday and broke today. Someone pasted new data over the table — pasted values often arrive as text or with spaces. Re-run the TRIM and ISNUMBER tests on the new data before touching the formula.
Tip
Wrap the whole thing in IFERROR once it works: =IFERROR(VLOOKUP(...),"Not found"). It does not fix anything, but it turns an ugly #N/A into a message your readers understand — and it tells you at a glance which rows genuinely have no match.
Practice file
Download the VLOOKUP Fixes practice file (.xlsx) — it contains a lookup table with the five classic traps (trailing spaces, text numbers, missing FALSE) and the fixed formulas next to the broken ones.
Google Sheets
Google Sheets VLOOKUP behaves the same way, including the need for FALSE. Sheets adds one convenience: IFERROR works identically, and the error message names the function, which makes debugging slightly easier.
Example prices and figures are illustrative, not market data.