You type a VLOOKUP, the value is right there in the table, and Excel still says #N/A. Or it returns a price from the wrong row, or works in
the first cell and falls apart when you copy it down. VLOOKUP is strict and it fails quietly, but it only fails in a handful of ways. Here they are, in
the order you are most likely to hit them.
How can you tell why VLOOKUP isn't working?
Find one value that should match and test the two cells directly. If your lookup value is in A2 and the matching row of the table is D7, type this in an empty cell:
=A2=D7 If it says FALSE, compare =LEN(A2) with =LEN(D7) (a longer one has extra spaces) and =ISNUMBER(A2) with =ISNUMBER(D7) (one is a number, the other text). If it says TRUE, the problem is in the
formula itself: work through causes 3 to 9 below.
| What you see | Most likely cause |
|---|---|
#N/A but the value is there | 1. Number vs text or 2. Hidden spaces |
| A value from the wrong row | 3. Approximate match |
| Works in row 2, breaks lower down | 4. Range not locked with $ |
#N/A for every row | 5. Key not in the first column |
#REF! or the wrong column | 6. Column number |
| The formula shows as text | 7. Cell formatted as Text |
| Old results that never change | 8. Manual calculation |
| Only the first of several matches | 9. Duplicate keys |
1. Why does VLOOKUP return #N/A when the values match?
The number one cause: the ID 1042 is a number in one table and the text "1042" in the other. They look identical, but Excel
treats them as different values. It happens all the time with data exported from other systems, and with codes that have leading zeros such as 00421. The giveaway is a small green triangle in the corner of the cells, or numbers that sit on the left of the cell instead of the right.
Convert the lookup value to match the table. If the table holds text, join an empty string to turn the number into text:
=VLOOKUP(A2&"",$D$2:$F$200,3,FALSE) If the table holds real numbers and your lookup value is text, use VALUE:
=VLOOKUP(VALUE(A2),$D$2:$F$200,3,FALSE) For a permanent fix, convert the whole column: select it, open Data, Text to Columns and click Finish, or use the warning triangle's Convert to Number. The numbers stored as text lesson covers both directions.
2. Why does VLOOKUP fail on names that look identical?
"Smith" and "Smith " are different values, and that trailing space is invisible. Data pasted from websites and PDFs often
carries non-breaking spaces too, which look like normal spaces but are a different character. Wrap the lookup value in TRIM to strip
leading, trailing and double spaces:
=VLOOKUP(TRIM(A2),$D$2:$F$200,3,FALSE) - TRIM(A2): lookup_value
- $D$2:$F$200: table_array
- 3: col_index_num
- FALSE: range_lookup
TRIM does not remove non-breaking spaces. Swap them for normal spaces first with =TRIM(SUBSTITUTE(A2,CHAR(160)," ")). And remember this only cleans the lookup value: if the spaces are in the table, clean that column too,
in a helper column or with the Trim and clean step in the Table Workspace. Practise it in Cleaning text with TRIM and PROPER.
3. Why does VLOOKUP return the wrong value?
If you leave out the last argument, VLOOKUP does an approximate match. It assumes the first column is sorted and returns the closest
value that is not bigger than the one you asked for. On an unsorted list of product codes that means a confident, wrong answer with no error to warn
you. Always end an exact lookup with FALSE (or 0):
=VLOOKUP(A2,$D$2:$F$200,3,FALSE) - A2: lookup_value
- $D$2:$F$200: table_array
- 3: col_index_num
- FALSE: range_lookup
4. Why does VLOOKUP break when you drag it down?
Copy =VLOOKUP(A2,D2:F200,3,FALSE) down one row and it becomes =VLOOKUP(A3,D3:F201,3,FALSE). The table slides down with
the formula, so by row 50 the first 48 rows of the table have dropped out of the range and those keys return #N/A. Lock the range with $ signs. Click inside the range while editing and press F4 to add them:
=VLOOKUP(A2,$D$2:$F$200,3,FALSE) This is the single most common mistake in lookups, so it has its own lesson: Locking the table range with $. If $ is new to you, start with Locking a cell with $.
5. Why can't VLOOKUP find a value in the table?
VLOOKUP only searches the first column of the range you give it, then counts right. If your codes are in column E and the range starts at D, it is searching the wrong column. And it can never return a column to the left of the key.
Start the range at the key column, or switch to XLOOKUP, which takes the search column and the return column separately and works in
either direction:
=XLOOKUP(A2,$E$2:$E$200,$D$2:$D$200,"Not found") - A2: lookup_value
- $E$2:$E$200: lookup_array
- $D$2:$D$200: return_array
- "Not found": if_not_found
See XLOOKUP vs VLOOKUP for everything else that changed. In Excel 2019 and earlier, use INDEX and MATCH instead (Looking left with INDEX and MATCH).
6. Why does VLOOKUP return #REF! or the wrong column?
col_index_num counts from the first column of your range, not from column A. For $D$2:$F$200, column D is 1 and F
is 3. Ask for column 4 and you get #REF!. Insert a column inside the table later and the number quietly points at the wrong data. Make it
follow the header instead:
=VLOOKUP(A2,$D$2:$F$200,MATCH("Price",$D$1:$F$1,0),FALSE) 7. Why does VLOOKUP show the formula instead of the result?
The cell was formatted as Text before you typed, so Excel stores what you typed as text. Change the format to General, then click into the cell, press F2 and Enter. If every formula on the sheet shows as text, Show Formulas is on: press Ctrl+` to turn it off. Also check there is no space or apostrophe before the =.
8. Why doesn't VLOOKUP update when the data changes?
The workbook is set to manual calculation. Go to Formulas, Calculation Options and choose Automatic. Pressing F9 recalculates once. Workbooks pick this setting up from whichever file was opened first, so it can switch without you touching it.
9. Why does VLOOKUP return only the first match?
That is how it is designed: VLOOKUP stops at the first row that matches. If a key appears twice, the second row is never returned. Count duplicates
with =COUNTIF($D$2:$D$200,A2). In Excel 365 you can return every match with FILTER:
=FILTER($F$2:$F$200,$D$2:$D$200=A2) - $F$2:$F$200: array
- $D$2:$D$200=A2: include
Should you hide #N/A with IFERROR?
Only after you know why the values are missing. IFERROR hides every error, including the mistakes above. Use IFNA, which
only catches the genuine "not found" case:
=IFNA(VLOOKUP(A2,$D$2:$F$200,3,FALSE),"Not found") How do you check a whole list at once?
Testing cells one by one gets slow with hundreds of rows. Paste both tables into Lookup & Merge and it matches them the way Excel
does, then explains every miss: "trailing space", "stored as a number in the other table", or "closest match: Jon Smith (92%)". It also writes the XLOOKUP and VLOOKUP formulas for your columns. Everything runs in your browser, so the data never leaves your computer.
To practise until it sticks, try the free VLOOKUP exact match lesson: it checks your formula and tells you which of these mistakes you made.