I need to replace the #N/A in an Excel spreadsheet with the value of zero. What is the correct syntex for this formula?
Please use cell references with the explanation and the formula.
Thanks.
2006-10-11 08:21:19 · 2 answers · asked by Sunny_1_ 3 in Computers & Internet ➔ Software
I need to replace the #N/A in an Excel spreadsheet with the value zero. What is the proper syntex?
Please use cell references and an explanation of the formula. The formula that is producing the error message is a vlookup formula.
How do I correct the #N/A and still use the vlookup formula?
2006-10-11 08:43:46 · update #1
You would use an IF statement. For instance, if you added 2 cells and got #N/A, you could make the formula like;
=if(iserror(A1+B1),"0",A1+B1)
In this case replace A1+B1 with your real formula.
2006-10-11 08:27:37 · answer #1 · answered by Hanged Man Bendy 3 · 0⤊ 0⤋
You can use this
=IF( ISNA(VLOOKUP( D1, Sheet1!A:G, 2, FASLE)), 0, VLOOKUP( D1, Sheet1!A:G, 2, FASLE))
I used here ISNA function which is special in #N/A error, you can replace it with ISERROR or any other information functions
Read my profile, I am the XLMan
2006-10-12 01:17:14 · answer #2 · answered by Anonymous · 1⤊ 0⤋