Learn Excel
EN
Sets how numbers, dates and amounts look in the exercises, and whether you type , or ; between arguments, just like your own Excel.

Home › Excel functions › VLOOKUP › #N/A error

VLOOKUP #N/A Error: Causes and Fixes

#N/A means "not available": VLOOKUP looked for your value and didn't find it. Sometimes that's correct, because the value really isn't in the list. But often the value is right there and VLOOKUP still can't see it. Here are the causes, from most to least common, and how to fix each one.

VLOOKUP · 3 min read · Try it yourself ↓
In this guide
  1. 7 reasons VLOOKUP returns #N/A
  2. Extra spaces
  3. Numbers stored as text
  4. Wrong range
  5. Hide #N/A with IFNA
  6. Try it yourself
  7. Other VLOOKUP errors
  8. More guides
  9. FAQ
Lina scratching her head at an error in her spreadsheet, with our mascot looking worried

7 reasons VLOOKUP returns #N/A

CauseHow to spot itFix
1. The value isn't thereSearch the list with Ctrl + F.Add the value, or show your own text with IFNA.
2. Extra spaces"Mango " with a space at the end looks like "Mango".Clean the data with TRIM.
3. Number stored as textA small green triangle in the cell, or the number is left-aligned.Convert the text to numbers.
4. Wrong first columnThe value is in your table, but not in the first column of the range.Start the range at the column you search in.
5. Range movedThe first rows work, lower rows give #N/A.Lock the range with $ signs.
6. Approximate match on unsorted dataThe last argument is TRUE or missing.Use FALSE for an exact match.
7. Approximate match below the smallest valueYour value is smaller than the first row of the table.Add a row with 0 at the top of the table.

Fix: extra spaces

Data copied from other systems often has spaces at the start or end. To you "Mango" and "Mango " look identical, but Excel sees two different values. You can check a cell with LEN: if =LEN(A5) gives 6 for "Mango", there's a hidden space.

The quickest fix is to clean the lookup value inside the formula:

=VLOOKUP(TRIM(E2), A2:C7, 3, FALSE)

If the spaces are in the table itself, add a column with TRIM, or clean the data once with Find and Replace.

Fix: numbers stored as text

An ID like 1004 can be stored as the number 1004 or as the text "1004". VLOOKUP treats them as different values. This happens a lot with exports from other software.

If the table has numbers and your lookup value is text, turn the lookup value into a number:

=VLOOKUP(VALUE(E2), A2:C7, 3, FALSE)

The other way round, when the table has text and you look up a number, join it with an empty text: E2&"" turns 1004 into "1004". For a permanent fix, see converting text to numbers.

Fix: the wrong first column or a moving range

VLOOKUP only searches the first column of the range you give it. If you look up a name in a range that starts at the ID column, it returns #N/A even though the name is in the table. Start the range at the column with the values you search for.

If the formula works in the first row but not further down, the range moved when you copied it: A2:C7 became A3:C8, then A4:C9. Lock it with $ signs, like $A$2:$C$7, so every copy searches the whole table.

Hide #N/A with IFNA or IFERROR

Sometimes #N/A is the right answer: a new product code that isn't in your list yet. Then you don't want an error in your report, but a clear message. Wrap the VLOOKUP in IFNA:

=IFNA(VLOOKUP(D2, A2:B6, 2, FALSE), "Unknown code")

IFNA only catches #N/A. IFERROR catches every error, including #REF! from a wrong column number, so it can hide mistakes in your formula. That's why IFNA is the safer choice for lookups. Use IFERROR if you also work with Excel 2010 or older, where IFNA doesn't exist.

Practice: show your own text instead of #N/A

The code in D2 isn't in the product list. Build a VLOOKUP inside IFNA so the cell shows "Unknown code" instead of #N/A.

Exercise: in cell E2, write =IFNA(VLOOKUP(D2,A2:B6,2,FALSE),"Unknown code") to show your own message. Turn on JavaScript to try it interactively.

Download the practice file (.xlsx)8 more VLOOKUP exercises →

Other VLOOKUP errors

  • #REF!: the column number is larger than the number of columns in your range. A range of A2:C7 has 3 columns, so 4 gives #REF!.
  • #VALUE!: the column number is smaller than 1, or the lookup value is longer than 255 characters.
  • #NAME?: the function name is misspelled, or text in the formula is missing its quotes.
  • A wrong value without an error: the last argument is missing, so VLOOKUP uses an approximate match. Add FALSE.

For all Excel errors in one place, see Excel formula errors and how to fix them.

More VLOOKUP guides

The complete guideFind a value in a table and return a matching value from the same row.Practice exercises8 exercises with tips: exact and approximate match, IFERROR, two conditions and another sheet.From another sheetPull data from a list on another tab or in another workbook.Multiple criteriaLook up a value that matches two conditions, like product and size.Approximate matchFind the right band in a sorted table: discounts, rates and grades.

Frequently asked questions

Why does VLOOKUP return #N/A when the value exists?

The values look the same but aren't: there's an extra space, one is a number and the other text, or the value isn't in the first column of your range. Check with LEN and ISNUMBER on both cells.

How do I replace #N/A with 0 or a blank cell?

Wrap the formula in IFNA and give the replacement as the second argument: 0 for zero, or two quotes ("") for an empty-looking cell.

Should I use IFNA or IFERROR with VLOOKUP?

IFNA, when you can. It only hides "not found", so real mistakes like a wrong column number still show up. IFERROR hides every error.

Why does VLOOKUP work in one row and give #N/A in the next?

Usually the table range moved when you copied the formula down. Lock it with $ signs, such as $A$2:$C$7.