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.
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:
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:
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.
- 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)) .
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.
- Use the IFERROR function in a structure
= IFERROR (VLOOKUP (J3, E2: H17,4,0), 0)
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
- 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.
- 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!
You should read it
- How to correct a #NAME error in Excel
- How to fix the SUM function doesn't add up in Excel
- What is ### error in Excel? how to fix ### error in Excel
- Instructions on how to fix reverse dates in Excel
- How to use the FIND function in Excel?
- how to fix error cannot copy data in Excel files
- How to fix the date #VALUE error in Excel
- How to fix date format errors in Excel
May be interested
- How to fix date format errors in Excelyou are new to excel and there are many strange problems. you are reversed the date format in excel. so how to fix this.
- How to correct when Excel automatically rounds numbersinstructions on how to quickly adjust excel to automatically round numbers. find out in tipsmake's article if this is a trick you're interested in
- Instructions to fix Excel column/row freezing not workingin some cases, you don't see the option to freeze excel columns or rows, which affects document processing and overall view of the data table.
- How to fix Vietnamese error when opening CSV file in Excelwhen converting excel file data into a csv file, you often encounter errors that do not display vietnamese fonts. this will undoubtedly have a small impact on your work. so how to fix the excel file error when converting to csv.
- Common causes of Excel workbook errorslost your work on excel due to file corruption? understanding the errors that cause damaged excel files will help you avoid that from happening.
- Fix Excel crashes in Windows 10since excel is developed by microsoft, you will have no problem using it on your windows computer. unfortunately; that's not entirely correct. sometimes excel hangs or freezes, leaving you with no chance to finish your work.
- How to fix date errors when copying to another Excel filewhen you copy the date to another excel file or change the date, it is 4 years and 1 day slower than the date in the file. so how to fix the error of displaying the wrong date when copying in excel?
- Fix Unable to unlink error in Excelinstructions on how to fix the error cannot be unlinked in excel, this is an error that users often encounter while using excel spreadsheets.
- How to fix the error of correct alignment but incorrect printing in Word?immediately refer to how to fix alignment errors that are correct but not printed correctly in word at a glance, shared by tipsmake in the article.
- How to fix reverse date errors in Excelthe dates are reversed in excel, the month before the next day makes vietnamese users feel strange and unfamiliar. strictly speaking, this is not an error but due to the different ways of writing foreign and vietnamese dates. to correct this you can follow the instructions below of tipsmake.vn.