Customize vlookup formula
 

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)

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200812/1

Customize vlookup formula
 

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)


--
Regards
Roger Govier

"AHizon via OfficeKB.com" <u38169@uwe wrote in message
news:8ede0adc8fcac@uwe...
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)

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200812/1

Customize vlookup formula
 

Try one of these:

=IF(ISNA(MATCH($A26,'Subobj Monthly
ExpensesFY09'!$A$8:$A$30,0)),0,VLOOKUP($A26,'Subob j 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,'Subob j Monthly
ExpensesFY09'!$A$8:$M$30,2,0),0)


--
Biff
Microsoft Excel MVP


"AHizon via OfficeKB.com" <u38169@uwe wrote in message
news:8ede0adc8fcac@uwe...
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)

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200812/1

Customize vlookup formula
 

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

Or, in Excel 2007:

=IFERROR(YourFormula,0)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"AHizon via OfficeKB.com" <u38169@uwe wrote in message
news:8ede0adc8fcac@uwe...
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)

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200812/1

Customize vlookup formula
 

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

Niek Otten wrote:
=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)

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200812/1