30 Advanced Excel Tips for 2026

Excel Champs / Excel Formulas List / #N/A Error in Excel
By Puneet Gogia (Microsoft MVP)

#N/A Error in Excel

30+ Advanced Excel Tips for 2026

No spam. Only Excel Stuff. God Promise. 😊

#N/A means “not available” — a lookup function searched for a value and didn’t find it. To replace it with your own message, wrap the formula in IFNA: =IFNA(VLOOKUP(A2,D:E,2,0),"Not found"). IFERROR does the same but catches every error type, so it will also hide a broken reference or a wrong column number — use IFNA when you only want to trap genuine misses.

Before hiding it, though, check why the match failed, because three causes account for nearly all of them. A trailing space in either the lookup value or the list (=TRIM(A2) to test). A number stored as text on one side and a real number on the other — they look identical but never match.

Or the fourth argument left out or set to TRUE, which does an approximate match and needs sorted data. In Excel 365, XLOOKUP has this built in as its fourth argument: =XLOOKUP(A2,D:D,E:E,"Not found").

And note that #N/A is sometimes deliberate — entering =NA() in a chart’s source data makes the line skip that point instead of dropping to zero.

[ec_form]

There are two situations when you are dealing with #N/A:

  1. You don’t want to have the #N/A error (you want to fix its cause).
  2. You don’t want to show it to the user (you want to hide it).

This tutorial will help you to deal with these situations.

#N/A Error – What Does It Mean?

As its name says, it means “Not Available”. In Excel, when you look for a value by using lookup functions and that value is not in the list or the range, your formula will return the result as an #N/A error.

na-error

When You Get the #N/A?

In the above example, we have used VLOOKUP to look up the value 7 in column A. But as you can see, “7” is not in the range, and the formula returns #N/A in the result.

when-you-get-na

In the same way, when you use the MATCH function and try to get a match for “7”, the formula returns #N/A in the result.

use-the-match-function

In addition, if you use another LOOKUP formula, such as HLOOKUP, LOOKUP, and INDEX + MATCH, you will also get the same error.

Note: With XLOOKUP, you have a built-in argument (if_not_found) that allows you to define the value to get if the value you are looking for is not in the lookup range.

=XLOOKUP(D1,A2:A11,B2:B11,"Not Found")

How to Deal with #N/A?

If you want to hide the error, there are two specific functions that you can use.

IFNA: Only for #N/A Error

In the example below, we used MATCH by wrapping it with IFNA. In IFNA, the first argument is the MATCH function, and the second is the value to return if MATCH returns #N/A.

=IFNA(MATCH(D1,A2:A11,0),"Not Found")

IFNA is available in Excel 2013 and later versions. As it only catches #N/A, other errors (like #REF! from a deleted column) still show up, so you don’t hide a real problem by mistake.

match-with-ifna

IFERROR: For All Errors

In the below example, we have also used IFERROR with VLOOKUP. We have wrapped VLOOKUP with IFERROR and specified the value to return if the result has an error.

=IFERROR(VLOOKUP(D1,A2:B11,2,FALSE),"Not Found")

Keep in mind that IFERROR hides every error, not only #N/A. If the formula breaks because of a wrong column number or a deleted range, you won’t see it, so use IFNA when you only expect #N/A.

iferror-with-vlookup

Other Points to Remember

  1. It’s better to verify the value you are looking for with any lookup function. If you have a misspelled lookup value while the correct value is in the range, it will be hard for you to spot it.
  2. Sometimes, you can get the #N/A error when the values you are looking for are in a different format than the ones you have in the lookup range. For example, if you are looking for a number but that number is saved as a text in the lookup range, you will still get the #N/A error.
  3. Having extra spaces in the lookup value or the values in the lookup table can also result in a #N/A error.

Fix the Common Causes of #N/A

  • Number stored as text: if the lookup range has “7” as text and you look up the number 7, join an empty string to the lookup value to make it text as well (formula below this list), or convert the range to numbers.
  • Extra spaces: use TRIM on the lookup value, or clean the lookup range with TRIM.
  • Range moved when copied: if the formula works in the first cell but not further down, the lookup range has moved. Lock it with dollar signs, like $A$2:$B$11.
  • Lookup value not in the first column: VLOOKUP only searches the first column of the table. Use INDEX + MATCH or XLOOKUP to look in any column.
  • Approximate match on unsorted data: with TRUE (or no fourth argument) in VLOOKUP, the first column must be sorted. Use FALSE for an exact match.
=VLOOKUP(D1&"",A2:B11,2,FALSE)

Sum a Range That Has #N/A Errors

SUM returns #N/A if even one cell in the range has the error. To sum the rest of the values, use AGGREGATE, which can skip error values (9 is for SUM and 6 is for ignoring errors):

=AGGREGATE(9,6,F1:F10)

On the other hand, #N/A can be useful in charts. A cell with =NA() is skipped in a line chart, so you don’t get a drop to zero for missing data.

Frequently Asked Questions

Why do I get #N/A when the value is in the list?

The two values look the same but aren’t: one is a number and the other is text, or one has an extra space or a hidden character. Check both with =ISTEXT() and =LEN().

How do I check if a formula returns #N/A?

Use ISNA. It returns TRUE only for #N/A, so you can use it in IF or in conditional formatting.

=ISNA(VLOOKUP(D1,A2:B11,2,FALSE))

Should I use IFNA or IFERROR?

Use IFNA for lookups, as a missing value is the only error you expect there. Use IFERROR only when any error should show the same result.

Puneet Gogia

Written by

Puneet Gogia

Microsoft MVP — Excel · Founder, Excel Champs

Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.

3M+ readers helped
1,000+ tutorials written
Since 2015 teaching Excel

Leave a Comment