N/A Error with VLOOKUP Formula - Excel 2007
in [Functions]
|
From: David on 27 Dec 2009 08:07 I have used the following formula to replace N/A with 0, where there is no value to return. =IF(ISNA(VLOOKUP(D18,'KOTC_Customer Price List '!A:E,2,FALSE), 0 ,VLOOKUP(D18,'KOTC_Customer Price List '!A:E,2,FALSE)) However, the formula does not register and the above formula just shows as text in the respective cell. I have even tried the IF ISERROR formula in Excel 2003 & 2007 and still have the same problem. Any ideas as to why this is happening, is much appreciated. Best regards, David Submitted via EggHeadCafe - Software Developer Portal of Choice The Software Project Loan Shark! http://www.eggheadcafe.com/tutorials/aspnet/86bc31c4-0983-432a-b68d-3d73e47db1d5/the-software-project-loan.aspx
From: Mike H on 27 Dec 2009 08:52 Hi, Small syntax error, try the formula below. A couple of points though:- While you can use full columns, unless it's necessary I wouldn't because it can get a bit slow. Your using column A - E in the lookup array and returning Column B why not use just 2 columns =IF(ISNA(VLOOKUP(D18,'KOTC_Customer Price List '!A:E,2,FALSE)),0,VLOOKUP(D18,'KOTC_Customer Price List '!A:E,2,FALSE)) Mike "David Scarfe" wrote: > I have used the following formula to replace N/A with 0, where there is no value to return. > > =IF(ISNA(VLOOKUP(D18,'KOTC_Customer Price List '!A:E,2,FALSE), 0 ,VLOOKUP(D18,'KOTC_Customer Price List '!A:E,2,FALSE)) > > However, the formula does not register and the above formula just shows as text in the respective cell. > > I have even tried the IF ISERROR formula in Excel 2003 & 2007 and still have the same problem. > > Any ideas as to why this is happening, is much appreciated. > > Best regards, > > David > > > Submitted via EggHeadCafe - Software Developer Portal of Choice > The Software Project Loan Shark! > http://www.eggheadcafe.com/tutorials/aspnet/86bc31c4-0983-432a-b68d-3d73e47db1d5/the-software-project-loan.aspx > . >
From: Mike H on 27 Dec 2009 08:56 Hi, On reflection, I prefer this =IF(COUNTIF('KOTC_Customer Price List '!A:A,D18)=0,0,VLOOKUP(D18,'KOTC_Customer Price List '!A:B,2,FALSE)) Mike "Mike H" wrote: > Hi, > > Small syntax error, try the formula below. A couple of points though:- > > While you can use full columns, unless it's necessary I wouldn't because it > can get a bit slow. > > Your using column A - E in the lookup array and returning Column B why not > use just 2 columns > > =IF(ISNA(VLOOKUP(D18,'KOTC_Customer Price List > '!A:E,2,FALSE)),0,VLOOKUP(D18,'KOTC_Customer Price List '!A:E,2,FALSE)) > > Mike > > "David Scarfe" wrote: > > > I have used the following formula to replace N/A with 0, where there is no value to return. > > > > =IF(ISNA(VLOOKUP(D18,'KOTC_Customer Price List '!A:E,2,FALSE), 0 ,VLOOKUP(D18,'KOTC_Customer Price List '!A:E,2,FALSE)) > > > > However, the formula does not register and the above formula just shows as text in the respective cell. > > > > I have even tried the IF ISERROR formula in Excel 2003 & 2007 and still have the same problem. > > > > Any ideas as to why this is happening, is much appreciated. > > > > Best regards, > > > > David > > > > > > Submitted via EggHeadCafe - Software Developer Portal of Choice > > The Software Project Loan Shark! > > http://www.eggheadcafe.com/tutorials/aspnet/86bc31c4-0983-432a-b68d-3d73e47db1d5/the-software-project-loan.aspx > > . > >
From: Ms-Exl-Learner on 27 Dec 2009 08:59 Your Formula is missing the closing parenthesis of ISNA Function. =IF(ISNA(VLOOKUP(D18,'KOTC_Customer Price List '!A:E,2,FALSE)),0,VLOOKUP(D18,'KOTC_Customer Price List '!A:E,2,FALSE)) Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "David Scarfe" wrote: > I have used the following formula to replace N/A with 0, where there is no value to return. > > =IF(ISNA(VLOOKUP(D18,'KOTC_Customer Price List '!A:E,2,FALSE), 0 ,VLOOKUP(D18,'KOTC_Customer Price List '!A:E,2,FALSE)) > > However, the formula does not register and the above formula just shows as text in the respective cell. > > I have even tried the IF ISERROR formula in Excel 2003 & 2007 and still have the same problem. > > Any ideas as to why this is happening, is much appreciated. > > Best regards, > > David > > > Submitted via EggHeadCafe - Software Developer Portal of Choice > The Software Project Loan Shark! > http://www.eggheadcafe.com/tutorials/aspnet/86bc31c4-0983-432a-b68d-3d73e47db1d5/the-software-project-loan.aspx > . >
From: Fred Smith on 27 Dec 2009 10:07 If the "formula just shows as text in the respective cell", then you have the cell formatted as Text. To fix this: -- Format the cell as General -- Re-enter the formula Regards, Fred "David Scarfe" wrote in message news:200912278710david.scarfe(a)alfalaval.com... >I have used the following formula to replace N/A with 0, where there is no >value to return. > > =IF(ISNA(VLOOKUP(D18,'KOTC_Customer Price List '!A:E,2,FALSE), 0 > ,VLOOKUP(D18,'KOTC_Customer Price List '!A:E,2,FALSE)) > > However, the formula does not register and the above formula just shows as > text in the respective cell. > > I have even tried the IF ISERROR formula in Excel 2003 & 2007 and still > have the same problem. > > Any ideas as to why this is happening, is much appreciated. > > Best regards, > > David > > > Submitted via EggHeadCafe - Software Developer Portal of Choice > The Software Project Loan Shark! > http://www.eggheadcafe.com/tutorials/aspnet/86bc31c4-0983-432a-b68d-3d73e47db1d5/the-software-project-loan.aspx
|
Next
|
Last
Pages: 1 2 Prev: Calculating Elapsed Time Over a Set Period Next: S- Curve Calculation and chart |