Hiding #N/A

Posted by Christi on May 25, 2001 6:27 AM

Here is the formula I am using:
=VLOOKUP(B47,'July model stock'!$1:$65536,2,FALSE)

Can anyone tell me what the If statement would be to hide the #N/A?? I have a huge file that I need to apply this to, and I can't seem to make it work.

Help, please!!

Posted by amroo on May 25, 2001 7:06 AM

Bonjour, see below your ask, the question isnammed:
Hidding #N/A, #Value!, ect... Forgot How... - Tom Woodyard 10:57:42 05/23/01

Posted by Scott on May 25, 2001 7:07 AM

There are a few ways to do this. The way that I always use is to insert the vlookup formula into a if, iserror formula. It would look like this:

=if(iserror(VLOOKUP(B47,'July model stock'!$1:$65536,2,FALSE)),0,VLOOKUP(B47,'July model stock'!$1:$65536,2,FALSE))

The ",0," tells it to return a "0" if there is an error, or in your case a #N/A. Hope this helps.

Posted by Aladin Akyurek on May 25, 2001 7:39 AM

Christi

Is $1:$65536 really the range of your lookup table on July model stock? Amazing.

Especially, with huge tables as yours, you wouldn't want #N/A's because hiding them can lead to slow performance. If you insist to get rid of them, I'd suggest using the following formula with, I believe, lesser performance cost:

=IF(ISNUMBER(MATCH(B47,July model stock!A:A,0)),VLOOKUP(B47,July model stock!$1:$65536,2,0),"")

I substituted the number 0 for FALSE in VLOOKUP, because as far as Excel concerned, FALSE=0 and TRUE=1.

Aladin

================== Here is the formula I am using: