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:

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

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),”")

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

[...] #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 [...]

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

Hello,

This is my function

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

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