Archive of Mr Excel Message Board

Back to Forms in Excel VBA archive index
Back to archive home

#n/A error message

Posted by Billy Burple on February 04, 2002 4:46 PM

In Excel - when i do a Vlookup i get a result of #n/A

Is there a formula something like =if(c34="#N/A", "", c34) to get rid of the N/A cos i cant use the cell to do sums etc.

Thanks

Check out our Excel Resources

Re: #n/A error message

Posted by Aladin Akyurek on February 04, 2002 4:53 PM

You can keep #N/A's and use SUMIF for summing instead of SUM as in:

=SUMIF(A2:A10,"<>#N/A")

or extend your formula for VLOOKUP to return zero instead of #N/A (see a few recent posts on this issue).

=============

Re: #n/A error message

Posted by Richard S on February 04, 2002 4:59 PM

Billy, give this a try.

Say you started with
=VLOOKUP(A2,R2:S100,2)
Change it to
=IF(ISNA(VLOOKUP(A2,R2:S100,2)),"",VLOOKUP(A2,R2:S100,2))

Richard

Re: #n/A error message

Posted by Billy Burple on February 04, 2002 5:09 PM

Thanks

I found the ISerror previous message and is working very well

Great Service !!

Care with ISERROR

Posted by Richard S on February 04, 2002 5:16 PM

Billy,
I used that also, but as Aladin pointed out to me, ISERROR catches all errors, not just N/A#, so you need to be a bit careful using it.
Richard Thanks I found the ISerror previous message and is working very well Great Service !!

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.