VLOOKUP not working? 9 causes and how to fix each one

Updated 19 November 2026

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
✎ FALSE means the cells look the same but are not the same, so VLOOKUP is right to say #N/A.

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 seeMost likely cause
#N/A but the value is there1. Number vs text or 2. Hidden spaces
A value from the wrong row3. Approximate match
Works in row 2, breaks lower down4. Range not locked with $
#N/A for every row5. Key not in the first column
#REF! or the wrong column6. Column number
The formula shows as text7. Cell formatted as Text
Old results that never change8. Manual calculation
Only the first of several matches9. 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
✎ FALSE = exact match. Approximate match is only for sorted bands, such as tax rates or grade boundaries.

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)
✎ A2 has no $ so it moves to A3, A4...; the table stays put.

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.

Revision Q&A

(click a question) Guide Q&A
Q1: Why does VLOOKUP return #N/A when the value is clearly in the table?

Almost always because the two values are not truly identical. The usual culprits are a number stored as text on one side, a trailing or non-breaking space, or a lookup column that is not the first column of the range. Test it with =A2=D7: if Excel says FALSE, the values differ.

Q2: Why does VLOOKUP work for some rows but not others?

Either the table range is not locked with $ and slides down as you copy the formula, or only some keys have stray spaces or are stored as text. Check the formula in a failing row first: if its range starts lower than the table does, add $ signs.

Q3: Why is VLOOKUP returning a value from the wrong row?

The fourth argument is missing or TRUE, so VLOOKUP does an approximate match and returns the nearest smaller value instead of an exact one. Add FALSE as the last argument.

Q4: Why does VLOOKUP return #REF!?

The column number is bigger than the number of columns in your table range. If the range is D2:F200 it has 3 columns, so col_index_num can only be 1, 2 or 3.

Q5: Why does VLOOKUP return #SPILL! in Excel 365?

The lookup value is a whole column or range, such as A:A, so Excel tries to return one answer per row and there is no room to spill them. Use a single cell such as A2 and copy the formula down.

Q6: Why is VLOOKUP not working between two workbooks?

Check the range still points at the right file and sheet. When the other workbook is closed, Excel stores its full path, so moving or renaming the file breaks the link. Update it under Data, Edit Links, or copy both tables into one workbook.

Q7: Why does SUM of my VLOOKUP results give #N/A or a total that is too low?

One lookup that finds nothing returns #N/A, and SUM passes that error on, so the total becomes #N/A. Wrap each lookup in IFNA(...,0) once you know the missing values are genuine. A total that is too low usually means a key appears in several rows and VLOOKUP only returns the first: use SUMIFS to add every matching row.

Q8: Why is my VLOOKUP with a wildcard not working?

Wildcards only work with an exact match, so the last argument must be FALSE, and only on text: a number in the lookup column never matches "12*". Put the wildcard in quotes and join it on, as in =VLOOKUP("*"&A2&"*",$D$2:$F$200,3,FALSE). To find a real * or ? in the data, put ~ in front of it. In XLOOKUP, wildcards need match_mode 2.

Q9: Should I wrap VLOOKUP in IFERROR to hide #N/A?

Only once you know why the values are missing. IFERROR hides every error, including a typo in the formula. IFNA is safer because it only replaces #N/A, which is the genuine "not found" case.

Q10: Do these fixes work in Google Sheets?

Yes. VLOOKUP behaves the same way in Google Sheets, and TRIM, VALUE, the $ signs and the FALSE argument all work identically.

Know someone fighting with a spreadsheet? Send them this page.