Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 23
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,480
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,440
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 23
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
customize count/sum formula for overdue dates AHizon via OfficeKB.com Excel Worksheet Functions 5 December 8th 08 06:55 PM
Customize Keyboard TychaBrahe Setting up and Configuration of Excel 2 March 22nd 08 12:33 AM
Calendar customize Carl R Excel Discussion (Misc queries) 1 July 24th 07 04:28 PM
Customize Formula Bar cashpgall Excel Worksheet Functions 2 August 18th 06 01:33 AM
Excel should allow me to customize the formula bar Transfig460 Setting up and Configuration of Excel 0 June 15th 05 07:59 PM


All times are GMT +1. The time now is 02:01 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"