Do you hate ‘#n/a’ in a Vlookup & Excel Formula?
Quick guide to remove any ‘#n/a Excel error’ in just seconds. The basics concepts are listed below.
- Formula to fix #n/a Excel – Worksheet functions used in fixing this error.
- VBA fix – How to fix Excel #n/a VBA error?
- Root Cause – Reason for Vlookup #n/a Excel error.
- Misc – Other error handling functions.
The best part is under header – “Root Cause”.
It explains the most common mistakes, that results in a ‘#n/a’ Vlookup error. If you know this, you wont need a formula to rectify this type of error.
1. How to Remove #N/A in Excel & Vlookup #n/a Errors?
These type of return values cannot be solved by using Excel “If #N/A” or “Excel na()”.
Seriously, then how to solve it?
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";
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. 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.1234567891011121314151617Private 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 #VALUE, similar to #n/a 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”.
In 2 steps – How to remove #n/a in Excel? – Fixing Vlookup #n/a