Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
customize count/sum formula for overdue dates | Excel Worksheet Functions | |||
Customize Keyboard | Setting up and Configuration of Excel | |||
Calendar customize | Excel Discussion (Misc queries) | |||
Customize Formula Bar | Excel Worksheet Functions | |||
Excel should allow me to customize the formula bar | Setting up and Configuration of Excel |