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

 Subscribe!

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. Duplicates - Finding and Removing in Excel There are many occasions when you may need to remove...
  4. Vlookup Multiple Criteria in Excel The standard vlookup function allows for one condition to be...
  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.

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

  1. JP on February 19th, 2009 at 5:14 pm

    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. Joseph Harris on February 20th, 2009 at 6:53 am

    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. Excel Yogi’s » Blog Archive » #VALUE! Error in Microsoft Excel on March 3rd, 2009 at 2:43 pm

    [...] #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. Amit on March 14th, 2009 at 6:06 am

    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

Leave a Reply