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.

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.