Back to Forms in Excel VBA archive index

Back to archive home

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

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

=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

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.

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

No Ian, I'll not get a penny. Hope they'll implement the thing.

Meanwhile, a shorter and nicer version, imho, would be:

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.

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.