 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / January 2008VLOOKUP challenge
Hi I am building a standard report template where the format does not alter. I pull information from another table into this standard report. The trouble is that the report I pull from changes so when I have a formula =VLOOKUP(A674,B11:AW673,11,0) and there is no information I get a #n/a error. I would like to have a 0 or blank returned to enable totalling to work as some cells have figures in them whilst others have n/a which prevents =SUM(F696:F697) etc from working. Cheers Brian =IF(ISERROR(VLOOKUP(A674,B11:AW673,11,0)),0,VLOOKUP(A674,B11:AW673,11,0))  SignatureGary''s Student - gsnu2007c Try something like this: =IF(COUNTIF(B11:B673,A674),VLOOKUP(A674,B11:AW673,11,0),0) Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) > Hi > [quoted text clipped - 10 lines] > > Brian On Jan 6, 3:25 pm, "Ron Coderre" <ronREMOVETHIScode...@bigfoot.com> wrote: > Try something like this: > [quoted text clipped - 29 lines] > > - Show quoted text - Hi Ron This works but I have an =sum(xxx) a couple of lines below referencing this cell and others but I am getting returned a 0 even when figures are in the other cells Ta Brian Hi Brian, the "numbers" in your data table are probably text values which just look like numbers - try this amendment to Ron's formula: =IF(COUNTIF(B11:B673,A674),VLOOKUP(A674,B11:AW673,11,0)+0,0) This should turn the values into numbers. Hope this helps. Pete On Jan 6, 6:58 pm, "brian.bake...@googlemail.com" <brian.bake...@googlemail.com> wrote: > On Jan 6, 3:25 pm, "Ron Coderre" <ronREMOVETHIScode...@bigfoot.com> > wrote: [quoted text clipped - 44 lines] > > - Show quoted text - > Hi Brian, > [quoted text clipped - 62 lines] > > - Show quoted text - Cheers Works a treat You're welcome. Pete On Jan 7, 7:52 am, "brian.bake...@googlemail.com" <brian.bake...@googlemail.com> wrote: > > Hi Brian, > [quoted text clipped - 68 lines] > > - Show quoted text - =IF(ISNA(vlookup formula),"",(vlookup formula) Returns a blank. Gord Dibben MS Excel MVP >Hi > [quoted text clipped - 10 lines] > >Brian On Jan 6, 1:16 pm, "brian.bake...@googlemail.com" <brian.bake...@googlemail.com> wrote: > Hi > [quoted text clipped - 10 lines] > > Brian Hi Gary This works but a cell that sums those above including a674 now returns an error how do I update =SUM(B672:B674). Ta Brian |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.