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),areturnvalueotherthan#N/A)
A zero or a blank is often used as areturnvalueotherthan#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 © 19982004 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.