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...ject-loan.aspx

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...ject-loan.aspx
.

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...ject-loan.aspx
.

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...ject-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

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...ject-loan.aspx
.

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,V LOOKUP(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...ject-loan.aspx
.

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
...
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...ject-loan.aspx