iserror formula help with vlookup

Posted by denis on January 14, 2002 1:44 PM

Can you please tell me how to use iserror with the vlookup below to stop #n/a appearing

=VLOOKUP(A1,C1:D100,2,0)

Thanks

Posted by Barrie Davidson on January 14, 2002 1:46 PM

=IF(ISNA(VLOOKUP(A1,C1:D100,2,0)),"WHAT YOU WANT IF ERROR ENCOUNTERED",VLOOKUP(A1,C1:D100,2,0))

BarrieBarrie Davidson

Posted by Jerid on January 14, 2002 1:52 PM

=IF(ISERROR(VLOOKUP(A1,C1:D100,2,0)),"",VLOOKUP(A1,C1:D100,2,0))

You could also use ISNA().

=IF(ISNA(VLOOKUP(A1,C1:D100,2,0)),"",VLOOKUP(A1,C1:D100,2,0))

Jerid

Posted by Barrie Davidson on January 14, 2002 1:54 PM

Jerid, I would use the second formula only. I used to use ISERROR until someone pointed out that you would want an error returned if the error was anything but an #N/A. This makes good sense to me, how about to you?

Regards,
BarrieBarrie Davidson

Posted by Richard S on January 14, 2002 1:56 PM

Use ISNA rather than ISERROR. ISERROR will detect ANY error, which may not be advisable. Refer previus posts by Aladin and Mark W re the dangers of this.
Richard
---------

Posted by Jerid on January 14, 2002 2:04 PM

I agree, and that was what I was going to originally send, but I started thinking, Richard asked how to use the IsError function, so I figured I would include that also.

Jerid

Posted by Aladin Akyurek on January 14, 2002 2:15 PM

Denis --

No ISERROR please. You'd rather not catch a #VALUE! error, which immensely instructive about your worksheet. The only thing you should care about (but see below) is #N/A. As Richard mentioned, ISNA is appropriate here, but with a hidden cost of computing the same thing twice. Since you're an exact match, you'd be (slightly) better of using:

=IF(COUNTIF(C1:C100,A1),VLOOKUP(A1,C1:D100,2,0),a-return-value-other-than-#N/A)

A zero or a blank is often used as a-return-value-other-than-#N/A.