1. How to Remove #N/A in Excel & Vlookup #n/a Errors?
#N/A is a difficult type of erroneous return code. It cannot be given as input as parenthesized text in a conditional statement in Excel “If #N/A” or “Excel na()”.
Then how to solve it?
You can use these simple Excel functions to capture a #n/a error.12345678910111213// Use one of these formula to remove #N/A Excel error=IFERROR (expression, value-if-error)=IsNA (expression)// How can you actually use this with your issue?// Use VLOOKUP inside the above formula to detect #n/a=IFERROR(VLOOKUP(1,2,1,FALSE),"0")--> Iferror will replace #n/a with 0;=ISNA(VLOOKUP(1,2,1,FALSE))--> IsNA will return "TRUE";
It is obvious that, IsNA is the best to use.
But wait – What is a Excel #n/a error actually means?
When a formula like Vlookup, could not find any valid return value, it results in #n/a. It indicates “Not Applicable” value.
Other similar error values starting with a hash are #Value, #DIV/0! , #Name? etc.,
What more tricks can you learn from this page?
2. How To Get rid of Excel #n/a errors in Vba Macro?
What if you are automating a Vlookup task & ended up with #n/a. No panic. Just use this syntax to remove #N/A Excel error in a VBA code.
Use the same IsNA or IFERROR formula. But this time, use it along with Application.Worksheetfunction before every Lookup command.
For example, use “Application.WorksheetFunction.IsNA” as given below.VBA Code to Remove #N/A in Excel ErrorVisual Basic1234567891011121314151617Private Sub Remove_NA_Excel_Error()Dim Ret as Boolean'Insert Formula that can give a erraneous return value - Simulate #n/aThisWorkbook.Sheets("sheet1").Cells(1, 1).FormulaR1C1 = "=VLOOKUP(1,2,1,FALSE)"ThisWorkbook.Sheets("sheet1").Cells(1, 2).FormulaR1C1 = "=NA()"'Verify & display Excel VBA #N/A Error ValueRet = Application.WorksheetFunction.IsNA(ThisWorkbook.Sheets("sheet1").Cells(1, 1))MsgBox "Return Value 1: " & Ret'Hide #n/a Excel ErrorRet = Application.WorksheetFunction.IsNA(ThisWorkbook.Sheets("sheet1").Cells(1, 2))MsgBox "Return Value 2: " & Ret'Code to delete cell showing Excel VBA #n/a error valueIf Ret = True Then ThisWorkbook.Sheets("Sheet1").Cells(1,2) = ""End Sub
Well, you mastered the art of handling a #n/a in Excel or Vlookup formula.
But wait, there’s more…
Don’t you want to know the secret of simulating a #N/A error? Refer this page to simulate different scenarios that will return #n/a error in its output to test all the 100% worst case scenarios. Here is a sample.
- Open a Excel workbook.
- Enter ‘=VLOOKUP(1,2,1,FALSE)’ in any worksheet cell.
- It will give you a ‘#n/a’ in Vlookup result.
Is that all? Want to know a secret?
The truth is – you wont need a formula at all, when you know how to avoid it.
3. Why We Get Vlookup #N/A Excel Errors
Well, it is going to be more explanations. I have categorized them as much as I could.
- Improper Syntax: Verify that the arguments used inside the formula are proper as per the syntax.
- Example: In a Vlookup function, the lookup columns must be the first column in the table. If we specify any other columns range, then we get such unexpected return codes.
- Data Format Mismatch: Ensure that data being searched is of same format from data in table.
- Example: If we search a number 123 in table that has values in text or string format, then we might get invalid return values.
- Extra Spaces: Verify that the values in lookup table does not have extra leading or trailing spaces.
- Solution: Use TRIM function to truncate the extra spaces in table values.
- #N/A in Input: Chances are there that your input lookup table itself has a #N/A value that is causing the error.
- Example: If we use a formula or reference within a lookup table to fetch value from another sheet or workbook, then it is possible that the input table itself has “#N/A” values.
- Solution: Use the function mentioned in this article to remove #n/a Excel error for table values itself.
So we could see that, it need not be always a formula error. It could also be one of the manual or unexpected data issues listed above. Verify them to fix Vlookup #N/A Excel error from your results.
Yes, I know. Too much in the checklist. Prevention is harder. Removing a Vlookup n/a Excel error from output is easier.
If the above checklist doesn’t give you any clue to solve your issue, then use the above mentioned solutions.
Is it only #n/a that could disturb you? No, there can be other variants too, like these below.
4. How to get rid of #n/a or #Value in Excel?
To eliminate #VALUE, #Div/0!, #Name?, #n/a Excel errors, we also have few more variants of above functions.
- Open a workbook & enter ‘=1/0’ in a worksheet .
- Excel will show #DIV/0! error.
It is not only a Vlookup function, but also, other Excel functions like Date, Text etc., could return these undesirable result that start with ‘#’. This is a result of an abnormal return value from a calculation when we attempt to divide a number by zero.
- To get rid of this, use “=ISERROR(1/0)”. This will return “TRUE” in case of any problem with calculation.
- Or use =IFERROR(1/0,”Error Occurred in Calculation”)
Solve Excel #N/A issue with Error Handling Functions
In the above image, IFERROR is used as =IFERROR(Value,”ERROR”). i.e., if Value has any error, it displays “ERROR” else it will display “Value”.
5. #N/A, #Value: Excel Error Handling Functions
Usual Syntax to hide #N/A in Excel formula is:
=IF( ISNA (Worksheet Formula), “Value if TRUE”, “Value if FALSE”)
In general, any error handling formula that starts with ‘IS’ will return a “True” or “False” & those starting with a “IF” are used to replace the output with a desired value.
In addition to the functions that we saw earlier, the below list of functions can also handle specific type of errors while using with Excel worksheet formula.
We have covered almost the causes and solution for this #n/a Excel error. It is now time to have some hands on. Create a new Excel workbook and test the codes mentioned in this article. Lets us know, if anything did not work as expected.
In case if you want to omit or handle the errors inside VBA, then the proper method is to handle it with a “On Error” event.
Every error can be captured with a “On Error Resume Next” and you can decide whether to proceed or process the exception in a different section of code with “On Error Goto LabelName”.
How to remove #n/a in Excel? – Fixing Vlookup #n/a