MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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


BarrieBarrie Davidson

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?

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.

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

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:


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

See also:


Also in the same thread is a a quite different approach, worth considering: