From: CellShocked on 27 Dec 2009 16:32 I would simply highlight the range in "KOTC_Customer Price List!" A:E And I would place my cursor up in the upper left corner of the workbook window and name the range KOTCCustPrcList. Then the formula is even shorter and the list becomes dynamic. =IF(ISNA(VLOOKUP(D18,KOTCCustPrcList,2,FALSE)),0,VLOOKUP(D18,KOTCCustPrcList,2,FALSE)) It also keep subsequent line/cell copying of the formula from morphing the range numbers. On Sun, 27 Dec 2009 05:59:01 -0800, Ms-Exl-Learner wrote: >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: user on 3 Jan 2010 00:12 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 >> . >> Download ASAP utilities from their website. Free utility that will do error correction for you. Excellent comprehensive utility that is indespensible. Naaman Dolphus First  |  Prev  |