Customize vlookup formula

[AHizon via OfficeKB.com]

Does anyone know how to customize a vlookup formula so that when it doesn't
see the particular data in the table instead of it displaying #N/A but 0?
Below is the formula I have but it puts #N/A in the cells that can't find the
match. Instead of doing a find and replace after I do a paste special, I'd
like the formula just to calculate it as 0. Can anyone help me with this
formula?

=VLOOKUP($A26, 'Subobj Monthly ExpensesFY09'!$A$8:$M$30,2, FALSE)

 

[Roger Govier]

Hi

If you are using Xl2003 or below then wrap the formula within an Iserror()
formula
=IF(ISERROR(VLOOKUP($A26, 'Subobj Monthly ExpensesFY09'!$A$8:$M$30,2,
FALSE)),
0,VLOOKUP($A26, 'Subobj Monthly ExpensesFY09'!$A$8:$M$30,2, FALSE))

If you are using Xl2007 then
=IFERROR(VLOOKUP($A26, 'Subobj Monthly ExpensesFY09'!$A$8:$M$30,2, FALSE),0)

 

[T. Valko]

Try one of these:

=IF(ISNA(MATCH($A26,'Subobj Monthly
ExpensesFY09'!$A$8:$A$30,0)),0,VLOOKUP($A26,'Subobj Monthly
ExpensesFY09'!$A$8:$M$30,2,0))

You might be able to use this slightly shorter version:

=IF(COUNTIF('Subobj Monthly
ExpensesFY09'!$A$8:$A$30,$A26),VLOOKUP($A26,'Subobj Monthly
ExpensesFY09'!$A$8:$M$30,2,0),0)

 

[Niek Otten]

=IF(ISNA(YourFormula),0,YourFormula)

Or, in Excel 2007:

=IFERROR(YourFormula,0)

 

[AHizon via OfficeKB.com]

Great, this formula worked...thanks so much!

=IF(ISNA(YourFormula),0,YourFormula)

Or, in Excel 2007:

=IFERROR(YourFormula,0)

Does anyone know how to customize a vlookup formula so that when it
doesn't

[quoted text clipped - 7 lines]

=VLOOKUP($A26, 'Subobj Monthly ExpensesFY09'!$A$8:$M$30,2, FALSE)