MrExcel Publishing
Your One Stop for Excel Tips & Solutions

#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


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).

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

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

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 !!

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

Care with ISERROR

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 !!