N/A Error with VLOOKUP Formula - Excel 2007
 

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

N/A Error with VLOOKUP Formula - Excel 2007
 

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
.

N/A Error with VLOOKUP Formula - Excel 2007
 

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
.

N/A Error with VLOOKUP Formula - Excel 2007
 

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
.

N/A Error with VLOOKUP Formula - Excel 2007
 

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

N/A Error with VLOOKUP Formula - Excel 2007
 

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
.

N/A Error with VLOOKUP Formula - Excel 2007
 

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