How to correct a #NA error in Excel

When working with formulas in Excel, sometimes you will get the # N / A error. So what is # N / A error, what is the cause of the error, and how to fix it? Please read the following article of Dexterity Software to better understand offline.

How to correct a #NA error in Excel Picture 1

What is # N / A error? The cause and mechanism of the error

In English N / A stands for the word Not Available, ie does not exist, is not available. The # N / A error in Excel indicates that a formula cannot find the content that the formula is requesting to find.

The # N / A error is often encountered when using search functions such as VLOOKUP, HLOOKUP, LOOKUP or MATCH when a formula cannot find the reference value. Consider the following example to better understand:

How to correct a #NA error in Excel Picture 2

In the above data table, the area to look up (Column E) does not have a value that matches the condition of 60V8705 , so the Vlookup function returns the # N / A error value .

How to fix error # N / A

The # N / A error is not a function error or a typo in the formula, it simply does not find the value you are looking for under a given condition. However, keeping the # N / A error value will prevent you from performing formulas later. For example:

How to correct a #NA error in Excel Picture 3

You need to calculate the total number of cement, but an # N / A error value will cause the total result of the whole range to be affected. So to overcome the above situation, you specify if the object cannot be found, then return empty (or equal to 0) by using the IF function or the IRERROR function.

1. Use the IF function to specify if no matching value is found, leave the value 0 and do not execute the formula using the following syntax:

 = IF (COUNTIF (E2: E17, J3) = 0,0, VLOOKUP (J3, E2: H17,4,0)) .

How to correct a #NA error in Excel Picture 4

Interpretation of the formula: The COUNTIF function will search between E2 and F17 how many conditions the value in cell J3 (In the example table above, the search condition is 60V8705). If the COUNTIF result is 0 (no match is found), it will return the cell result to 0, if it is different from 0 (the condition is found), the search formula will be performed. for VLOOKUP.

1. Use the IFERROR function in a structure

= IFERROR (VLOOKUP (J3, E2: H17,4,0), 0)

How to correct a #NA error in Excel Picture 5

Formula interpretation: Excel will run the VLOOKUP formula first, the IFERROR function will check the result of the VLOOKUP function.If the result is an error, the IFERROR function will return 0, otherwise it will display the result of the function. VLOOKUP.

Some special cases

1. In some cases, the # N / A error result is not due to the search data you need not being found, but because you entered an incorrect condition. For example, you need to find the license plate number 60V8704 but type the wrong number 60V8705.
2. The data to look for though the naked eye is the same but actually different in the form of data such as the cases: spaces, leading spaces, or different data formats .

In the above cases, you must check the conditional character, because the excess character also leads to data inconsistency error, causing an error that does not exist for the data to be searched.

Good luck!