Prev: Calculating Elapsed Time Over a Set Period
Next: S- Curve Calculation and chart
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
<Ms.Exl.Learner(a)gmail.com> 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  | 
Pages: 1 2
Prev: Calculating Elapsed Time Over a Set Period
Next: S- Curve Calculation and chart