Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

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


Check out our Excel Resources

Re: iserror formula help with vlookup

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


Re: iserror formula help with vlookup

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


Re: iserror formula help with vlookup

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


Re: iserror formula help with vlookup

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


Re: iserror formula help with vlookup

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


Re: iserror formula help with vlookup

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.

See also:

14969.html

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

Aladin

========


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.