MrExcel.com - Your One Stop for Excel Tips & Solutions

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.

Posted by Eric on July 12, 2001 7:20 AM

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

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

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.

Posted by Eric on July 12, 2001 7:24 AM

Oops, parentheses error, here's the fix

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

Posted by Russell on July 12, 2001 7:45 AM

Thanks - the fix

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

Posted by Aladin Akyurek on July 12, 2001 8:48 AM

A nicer version to return a zero & ms

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.

Aladin