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, or paste it into the Formula Explainer, which reads it step by
step and flags causes 3, 4 and 6 for you.
| 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.
Here the 1042 you're looking for is text, and the 1042 in the table is a number:
- A2 text "1042": sits on the left of the cell
- D3 the number 1042: sits on the right
- B2 so they never match
=VLOOKUP(VALUE(A2), $D$2:$F$4, 3, FALSE) →2.25
Convert the lookup value to match the table. That's what VALUE did above: it turns text that looks like a number into a real number. If
it's the other way round (the table holds text), join an empty string to turn the number into text:
=VLOOKUP(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. The dot below marks where the invisible space is:
- A2 "Smith" with a space after it (shown as ·)
- D3 "Smith" with no space
- B2 not the same text, so #N/A
=VLOOKUP(TRIM(A2), $D$2:$F$4, 3, FALSE) →305
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. That means a confident, wrong answer with no error to warn you. Here P-107 isn't in the list at all, and VLOOKUP hands back the price of P-105 instead:
- A2 P-107 is not in the list
- D6:F6 the closest smaller code, used instead
=VLOOKUP(A2, $D$2:$F$6, 3) →6.50 ✗
On an unsorted list it's worse: it can return a value from almost any row. Always end an exact lookup with FALSE (or 0), and
a missing code gives an honest #N/A:
=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: how INDEX MATCH works, or practise it in 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. When the range starts at column D, D is 1, E is 2
and F is 3:
- D2:F4 the range: column D counts as 1
- F3 column 3 of the range
=VLOOKUP("P-102", $D$2:$F$4, 3, FALSE) →3.99
Ask for column 4 and you get #REF!, because the range only has three. Insert a column inside the table later and the 3 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:
- D2:E2 first Ana: the one you get
- D4:E4 second Ana: never returned
=VLOOKUP("Ana", $D$2:$E$4, 2, FALSE) →1001
Count duplicates with =COUNTIF($D$2:$D$200,A2): anything above 1 appears more than once. 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
What if SUM with VLOOKUP gives #N/A or the wrong total?
Adding up a column of lookups goes wrong in three ways. The first is the loudest: one code that isn't in the table gives #N/A, and SUM
passes the error on, so the whole total turns into #N/A:
- A3 P-109 is not in the table
- B3 one #N/A...
- B5 ...and the whole total is #N/A
=SUM(IFNA(VLOOKUP(A2:A4, $D$2:$F$4, 3, FALSE), 0)) →3.45
Fix the cause first (one of the nine above). If some codes really are missing, count them as 0 with IFNA in each row, or wrap the whole
thing in one formula as shown under the sheet. Second, the total is too low with no error: if a key appears in several rows, VLOOKUP only ever returns
the first one, so summing VLOOKUPs misses the rest. To add every matching row, use SUMIFS instead:
- E2 VLOOKUP stops here: 120
- E4 SUMIFS adds this one too
=SUMIFS($E$2:$E$4, $D$2:$D$4, "Ana") →165
Third, the total is too low or 0 because the looked-up values are numbers stored as text: SUM skips text without a warning. Turn them into
numbers as in cause 1, then check the total by selecting the cells and reading Sum and Count in the status bar. The fixing a broken total lesson lets you practise all three.
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.