#N/A means “not available”: VLOOKUP looked for your value and didn’t find it. Sometimes that’s correct, the value really isn’t there. Far more often the value is there and a small mismatch is hiding it: an approximate match instead of an exact one, a number stored as text, a stray space, or a lookup pointed at the wrong column.
The quickest fix to try first is an exact match. Add FALSE as the fourth argument: =VLOOKUP(D2, A2:C5, 3, FALSE). If that doesn’t do it, work down the short list of usual causes below. Each one has a specific, repeatable fix.
What does #N/A mean in a VLOOKUP?
VLOOKUP searches for your value in the first column of the table you hand it, then returns a value from a column further right. When it can’t find the value in that first column, it returns #N/A.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | ID | Item | Price | Find ID | Price |
| 2 | 1001 | Pencils | 2.50 | 1005 | #N/A |
| 3 | 1002 | Pens | 3.00 | ||
| 4 | 1003 | Erasers | 1.20 | ||
| 5 | 1004 | Markers | 4.50 |
That is the honest case: 1005 genuinely isn’t in the list, so #N/A is the right answer. The confusing case is when the value you’re looking for is plainly sitting in the table and you still get #N/A. That comes down to one of the causes below.
Why is my VLOOKUP returning #N/A when the value is there?
1. You need an exact match (the missing FALSE)
This is the most common surprise. VLOOKUP’s fourth argument, range_lookup, decides how it matches. Leave it out and it defaults to an approximate match, which assumes the first column is sorted in ascending order and returns the closest value at or below what you asked for. On unsorted data that quietly produces #N/A or, worse, the wrong row’s value.
Microsoft’s guidance is direct: to find an exact match, set range_lookup to FALSE. With FALSE, the order of the table no longer matters and a miss is an honest miss.
2. The lookup value isn’t in the first column
VLOOKUP can only look in the leftmost column of the range you give it. If the value you’re matching on sits in a column to the right of the answer you want, VLOOKUP can’t see it, and you get #N/A.
You can rearrange the columns so the lookup value is on the left. Often the cleaner fix is XLOOKUP, which looks in any column and returns from any other, in either direction:
That looks for D2 in column C and returns the matching value from column A, to its left, which VLOOKUP can’t do.
3. Numbers stored as text (or text stored as numbers)
A lookup value of the number 1005 will not match a cell containing the text "1005", even though they look identical. This is the sneakiest cause because nothing on screen gives it away. Excel often flags text-numbers with a small green triangle in the top-left corner of the cell.
The fix is to make both sides the same type. Select the text-numbers and use Convert to Number, or reformat the column and re-enter the values, so the lookup value and the table column are both numbers (or both text).
4. Extra spaces or hidden characters
Values pasted in from another system often carry a trailing space ("1005 ") or a non-printing character. To Excel that is a different string, so the match fails. The fix is TRIM, which removes leading and trailing spaces. Microsoft’s own suggestion nests it inside the lookup:
Two notes on that formula. The A2:B7 range and the column number 2 are Microsoft’s own two-column example, so swap in your table’s range and column. And in versions of Excel without dynamic arrays, it has to be entered as an array formula, confirmed with Ctrl+Shift+Enter rather than Enter.
Many people find it simpler to clean the lookup column once with TRIM in a helper column, then point VLOOKUP at the cleaned column. Either way, the goal is the same: no stray characters on either side of the match.
How to handle a #N/A you actually expect
Sometimes no match is a perfectly valid outcome: a new product code that isn’t in the price list yet, a name that hasn’t been added. Leaving a raw #N/A in a report looks broken and breaks any sum that touches it. Wrap the lookup so it shows something sensible instead.
The right tool here is usually IFNA, which returns your fallback value only when the formula produces #N/A:
IFERROR does a similar job but catches every error type, not just #N/A. That convenience is also its risk: an =IFERROR(VLOOKUP(...), 0) turns a genuinely broken formula, a #REF! from a deleted column, say, into a silent 0 that sails through review and lands in a total. Reach for IFNA when you specifically mean “no match,” and keep IFERROR for when you truly want to swallow anything.
#N/A vs #REF! vs #VALUE!: a VLOOKUP can throw all three
#N/A is only one of the errors VLOOKUP can return, and they mean different things:
#N/A: the lookup value wasn’t found in the first column. That’s this article.#REF!: the column number you asked for is greater than the number of columns in the table. You told VLOOKUP to return column 4 of a three-column table.#VALUE!: the column number is less than 1.
If you’re seeing #REF! or #VALUE! rather than #N/A, check the third argument (the column index) before anything else. For the rest of Excel’s error vocabulary, see Excel errors explained.
When to switch to XLOOKUP or INDEX/MATCH
If you keep fighting VLOOKUP, two alternatives sidestep most #N/A traps:
- XLOOKUP looks in any column and returns from any other, so the leftmost-column rule goes away. It also has a built-in fourth argument for the not-found case, so you don’t need to wrap it in IFNA. Where a match isn’t found, it returns the text you supply.
- INDEX/MATCH does the same job in any version of Excel.
MATCHfinds the row,INDEXreturns the value from whichever column you choose:
The 0 in MATCH means exact match, the same idea as FALSE in VLOOKUP. Because neither formula counts columns from the left, inserting a column into the middle of your table won’t quietly break them the way it breaks a VLOOKUP with a hard-coded column number.
The full trade-offs, and a worked conversion from one to the other, are in VLOOKUP vs XLOOKUP: which should you use?
The #N/A worth worrying about
A visible #N/A is the harmless kind of error: it’s loud, it’s obvious, and someone fixes it. The dangerous version is a #N/A that’s been “fixed” by wrapping the whole formula in IFERROR(..., 0), which converts a broken lookup into a confident, wrong number that no longer looks like an error at all. Quiet formula mistakes of exactly this kind are what the European Spreadsheet Risks Interest Group (EuSpRiG) has spent years cataloguing in real organisations.
So fix the cause, not just the symptom. And when you change a column of lookups in a model other people depend on, compare the workbook before and after to confirm you fixed the number rather than moving it somewhere you can’t see.
