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
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))
Posted by Jerid on January 14, 2002 1:52 PM
You could also use ISNA().
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?
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.
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.
Posted by Aladin Akyurek on January 14, 2002 2:15 PM
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:
A zero or a blank is often used as a-return-value-other-than-#N/A.
Also in the same thread is a a quite different approach, worth considering: