Nesting an iserror function with a vlookup

Posted by Deb on July 30, 2001 9:29 AM

When I use a vlookup, and the value I am looking for does not exist in the source data, I get back a #N/A. I know I can use an =iserror function to find those and replace them with 0, but what's the syntax??

I have tried nesting the =iserror function inside an if statement, but I can't get the parens in the right places, apparently.

Posted by Aladin Akyurek on July 30, 2001 9:53 AM

Deb,

ISERROR is a function that does a catch-all test. No need for that in combination with lookup.

Ordinarily, you use the ISNA function for the task you describe. The syntax is:

=IF(ISNA(VLOOKUP(...)),0,VLOOKUP())

Another (a nice way) of doing it is using ISNUMBER in combination with MATCH.

lets say you have your lookup table in A1:C10 on sheet X. The following formula will work just like the previous one:

=IF(ISNUMBER(MATCH(lookup-balue,X!$A$1:$A$10,match-type)),VLOOKUP(lookup-value,X!$A$1:$C$10,col-index,match-type),0)

Aladin