# Archive of Mr Excel Message Board

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

## VLOOKUP - can FALSE test return a value of zero?

Posted by Russell Thames on July 12, 2001 7:13 AM
In using the VLOOKUP function I would like to have the test return a value of zero to the cell when it comes up false. For instance, right now my command looks like this =VLOOKUP(\$C7,'02-27-01'!\$C\$7:\$P\$300,14,FALSE), but the FALSE returns "#N/A" to the cell, which prevents further calculations made on that column/row. I would like to have it return a numerical value 0. Any suggestions would be greatly appreciated.

 Check out our Excel Resources

## put the vlookup statement in an if statement and use iserror to detect the #N/A

Posted by Eric on July 12, 2001 7:20 AM
try
=if(iserror(VLOOKUP(\$C7,'02-27-01'!\$C\$7:\$P\$300,14,FALSE)=true,0,(VLOOKUP(\$C7,'02-27-01'!\$C\$7:\$P\$300,14,FALSE))
that should replace the vlookup formula result with a 0 if the formula result is an error

## Re: VLOOKUP - can FALSE test return a value of zero?

Posted by IML on July 12, 2001 7:21 AM

You could use something along the lines of
=IF(NOT(ISNA(VLOOKUP(a1,range,2,FALSE))),VLOOKUP(aq,range,2,FALSE),0)

Aladin had suggested to MS a fifth argument allowing a default value in lieu of n/a a few months again. Did you ever hear anything back? I looked at the form and it looks like MS made it clear you wouldn't get rich from it!

Good luck.

## Oops, parentheses error, here's the fix

Posted by Eric on July 12, 2001 7:24 AM
=if(iserror(VLOOKUP(\$C7,'02-27-01'!\$C\$7:\$P\$300,14,FALSE)=true),0,VLOOKUP(\$C7,'02-27-01'!\$C\$7:\$P\$300,14,FALSE))

## Thanks - the fix

Posted by Russell on July 12, 2001 7:45 AM
Thanks to IML and Eric for your responses. Eric's suggestion worked perfectly: =if(iserror(VLOOKUP(\$C7,'02-27-01'!\$C\$7:\$P\$300,14,FALSE)=true),0,VLOOKUP(\$C7,'02-27-01'!\$C\$7:\$P\$300,14,FALSE))

## A nicer version to return a zero & ms

Posted by Aladin Akyurek on July 12, 2001 8:48 AM
No Ian, I'll not get a penny. Hope they'll implement the thing.
Meanwhile, a shorter and nicer version, imho, would be:

=IF(ISNUMBER(MATCH(\$C7,'02-27-01'!\$C\$7:\$C\$300,0)), VLOOKUP(\$C7,'02-27-01'!\$C\$7:\$P\$300,14,0),0)

The first arg of MATCH and VLOOKUP (i.e., \$C7) is I presume on a different sheet.

Take care.