2 Methods To - Remove #N/A Error in Excel - Vlookup

1. How to Remove #N/A error in Excel Formula & Vlookup NA Errors?

To remove #N/A error, first You should find a way to detect it within the Excel formula. Unfortunately, this error cannot be detected directly by mentioning #NA with a conditional statement in Excel like: "If #N/A" or "Excel na()". Intead, use one of these simple Excel functions to capture & remove #n/a error.

// Use one of these formula to remove #N/A Excel & Vlookup 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. Best part is that this can be used with Vlookup return values also. But wait - What is a Excel #n/a error actually means? Knowing this would help in preventing the issue. When a formula like Vlookup, could not find any valid return value, it results in #n/a error.  It indicates a "Not Applicable" value. Other similar error values starting with a hash are #Value, #DIV/0! , #Name? etc.,

2. #N/A, #Value: Excel Error Handling Formula

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.

• IsErr
• IsEven
• IsOdd
• IsNA
• IsText
• IsNumber
• IsNonText
• IsLogical
• IsRef

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".

3. [VBA] How To Get rid of Excel #n/a errors?

When you are automating a Vlookup task & ended up with #n/a. Don't 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.

Private Sub Remove_NA_Excel_Error()
    Dim Ret as Boolean
    'Insert Formula that can give a erraneous return value - Simulate #n/a
    ThisWorkbook.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 Value
    Ret = Application.WorksheetFunction.IsNA(ThisWorkbook.Sheets("sheet1").Cells(1, 1))
    MsgBox "Return Value 1: " & Ret
    
    'Hide #n/a Excel Error
    Ret = Application.WorksheetFunction.IsNA(ThisWorkbook.Sheets("sheet1").Cells(1, 2))
    MsgBox "Return Value 2: " & Ret
    
   'Code to delete cell showing Excel VBA #n/a error value
   If 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.

1. Open a Excel workbook.
2. Enter '=VLOOKUP(1,2,1,FALSE)' in any worksheet cell.
3.  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.

4. [Cause] Vlookup #N/A Error in Excel Formula?

Well, here are few explanations for this kind of errors. I have categorized them as much as I could.

1. 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.
2. 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.
3. 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.
4. #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 na that could disturb you? No, there can be other variants too, like these below.

5. [More] How to remove #Value in Excel?

To eliminate #VALUE, #Div/0!, #Name?, #n/a Excel errors, we also have few more variants of above functions.

1. Open a workbook & enter '=1/0' in a worksheet .
2. 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 NA 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".