# Removing #N/A error in Excel using ISNA()

The #N/A error happens most often when a lookup function cannot find a result. You can manually remove the all of your #N/As or you can stop them in your original formula. A simple and effective way of trapping the #N/A errors and replacing them with whatever you would like is to use the ISNA() function.

ISNA() is a simple function that checks to see if a cell contains N/A. If it does it returns TRUE, if not it returns FALSE. For example, cell A1 has #N/A, in B1 you type ISNA(A1). The result would be TRUE. We can use this function in conjunction with other functions to test if a result will be NA before we see the result. For Example:

=VLOOKUP(E4,\$A\$4:\$B\$6,2,FALSE)

The function above looks in the table a4:b6 for whatever is in E4, if it finds it, it returns a result from column 2 of the table. If it does not find a result, it returns #N/A. We can stop the results from showing #N/A by using ISNA as below:

=IF(ISNA(VLOOKUP(E4,\$A\$4:\$B\$6,2,FALSE)),“”,VLOOKUP(E4,\$A\$4:\$B\$6,2,FALSE))

The formula above shows the result in the highlighted red section rather than #N/A. In this case it is showing a NULL (“”).

Joseph Harris
Excel Help
More Excel Help

Related posts:

1. #VALUE! Error in Microsoft Excel The #VALUE error is an error that is seen almost...
2. Vlookup – My Favorite Function Vlookup...
3. How to Use ISERROR Function in Excel The ISERROR function is a versatile one that is very...
4. Duplicates – Finding and Removing in Excel There are many occasions when you may need to remove...
5. Excel CELL () Function The CELL function can be used to find out information...

This entry was posted on Tuesday, February 17th, 2009 at 3:07 pm and is filed under Excel Hints, Functions. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

## 6 Responses to “Removing #N/A error in Excel using ISNA()”

1. Why not use COUNTA to check if the value is present first, otherwise you are doing VLOOKUP twice?

=IF(COUNTA(E4,\$A\$4:\$B\$6),VLOOKUP(E4,\$A\$4:\$B\$6,2,FALSE),”")

2. Sure JP,
You can use COUNTA…There are many more methods than that as well. I was attempting to point out what ISNA() can be used for. Your counta formula saves 4 or 5 characters over the ISNA method.

Joe

3. [...] #VALUE error is an error that is seen almost as often as #N/A. In short, the VALUE error occurs when a formula refers to a cell that contains a different than [...]

4. Hi,

I am in a big problem. I have this code

=IF(C10=”Distributor-VAT”,VLOOKUP(B13,K1:L1000,2,0),IF(C10=”Distributor-CST”,VLOOKUP(B13,M1:N1000,2,0),IF(C10=”Distributor-EW”,VLOOKUP(B13,O1:P1000,2,0),IF(C10=”Distributor-MH”,VLOOKUP(B13,Q1:R1000,2,0),IF(C10=”Dealer-VAT”,VLOOKUP(B13,S1:T1000,2,0),IF(C10=”Dealer-CST”,VLOOKUP(B13,U1:V1000,2,0),IF(C10=”Dealer-EW”,VLOOKUP(B13,W1:X1000,2,0),VLOOKUP(B13,Y1:Z1000,2,0))))))))

This cell already have 7 IF’s so I cannot use ISERROR. Any idea how to get #N/A issue sorted.

Thanks
Amit

Please e-mail me the response if you know how to do it @

amit.ganotra@yahoo.com

5. Hello,

This is my function
IF(ISNA(VLOOKUP(AT2,users!A2:B66,2,FALSE)),”NULL”,VLOOKUP(AT2,users!A2:B66,2,FALSE))

6. Hello,

This is my function
IF(ISNA(VLOOKUP(AT2,users!A2:B66,2,FALSE)),”NULL”,VLOOKUP(AT2,users!A2:B66,2,FALSE))
users is another sheet in the excel, now when I drag this function, I want only AT2 to changes as per the cell but not users!A2:B66 to become users!A3:B67. There are some million records in the other sheet, so I don’t get these values right. Can you tell me how to fix this.

Thank You
Sai