Fix #n/a Excel using Worksheet Formula
Learn these very simple ways to fix #n/a Excel or Vlookup #n/a error with below listed topics.
- Root Cause: Reason for Vlookup #n/a Excel error.
- Formula to Fix #n/a: Worksheet functions used in fixing these errors.
- VBA Fix: How to fix Excel #n/a VBA error?
- Misc: Other error handling functions.
Excel #n/a error means that a function could not find any valid return value from the input lookup table. It indicates a “Not Applicable” value. Other error values starting with a hash like #VALUE, #DIV/0! , #Name? etc., are also possible output from many Excel functions.
I am not going to lie. All these issues can be fixed with just one simple formula. Let’s explore more.
1. Root Cause for Vlookup #N/A Excel Error
Before removing a Vlookup n/a Excel error from output, lets understand what caused such errors.
- 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.
If the above checklist doesn’t give you any clue to solve your issue, then proceed further.
2. Formula Fix: How to Remove #N/A Error in Excel vlookup?
It is not only a Vlookup function, but also, other Excel functions like Date, Text etc., could return undesirable text result that start with ‘#’. These type of return values cannot be solved by using a plain Excel “If #N/A” or “Excel na()”.
To hide them from your results, embed Vlookup & other such functions, within one of the below two Error handling functions as explained.
Visual Basic123#N/A Excel error can be removed by using one of the below listed formula:1. Vlookup function within =IFERROR(VLOOKUP(1,2,1,FALSE),"0") --> This will replace #n/a with 0;2. IsNA as =ISNA(VLOOKUP(1,2,1,FALSE)) --> This will return "TRUE";
- Open a Excel workbook.
- Enter ‘=VLOOKUP(1,2,1,FALSE)’ in any worksheet cell.
- ‘#N/A’ in Vlookup result will be displayed.
- Enter command as below to avoid this is error,
This will replace the Excel #N/A error with a chosen value. To test these error handling function, refer this page to simulate different scenarios that will return #n/a error in its output.
3. Macro to Hide Excel VBA #n/a Error Value
The syntax to remove #N/A Excel error in a VBA code with ISERROR or IFERROR is the same, but it has to be used along with Application.Worksheetfunction before every Lookup command.
These Error handling routines can be invoked as “Application.WorksheetFunction.IsNA” as given below.Visual Basic1234567891011121314151617Private Sub Remove_NA_Excel_Error()Dim Ret as Boolean'Insert Formula that can give a erraneous return valueThisWorkbook.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
This way the unexpected errors during a VBA code execution can be captured in any variable. 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”.
4. How to get rid of #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.
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”.
Excel Error Handling Functions Summary
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 didnot work as expected.
Previous Post: Excel VBA AutoFilter Multiple Criteria in Array or List
Next Post: Remove Junk Data From Text File
Remove #N/A Excel Error – Replace #N/A in VLOOKUP with Valid Value