Remember Me?

#1
October 28th 05, 01:40 PM
 marketingman Posts: n/a
How do I replace a return value of #N/A in a vlookup with zero?

Rather than #N/A being returned from a lookup for a value that is not in the
lookup table can I replace the returned #N/A with a zero?

Without being able to do this I am unable sum the returned values.

#2
October 28th 05, 01:46 PM
 CLR Posts: n/a
How do I replace a return value of #N/A in a vlookup with zero?

IF(ISNA(YourLookupFormula),0,YourLookupFormula)

Vaya con Dios,
Chuck, CABGx3

"marketingman" wrote:

Rather than #N/A being returned from a lookup for a value that is not in the
lookup table can I replace the returned #N/A with a zero?

Without being able to do this I am unable sum the returned values.

#3
October 28th 05, 03:35 PM
 mbarron Posts: n/a
How do I replace a return value of #N/A in a vlookup with zero?

Try this:

#4
October 28th 05, 05:18 PM
 Ashish Mathur Posts: n/a
How do I replace a return value of #N/A in a vlookup with zero?

Hi,

To sum the values ignoring the error values, you may want to use the
following formula. You need not alter your vlookup formula

In range A1:A4, you have the following

1
2
3
#DIV/0!

In cell A6, array enter (Ctrl+Shift+Enter) the following formula

SUM(IF(NOT(ISERROR(A1:A4)),A1:A4))

Regards,

Ashish Mathur

"marketingman" wrote:

Rather than #N/A being returned from a lookup for a value that is not in the
lookup table can I replace the returned #N/A with a zero?

Without being able to do this I am unable sum the returned values.

#5
October 28th 05, 09:45 PM
How do I replace a return value of #N/A in a vlookup with zero?

1.

=SUM(Range,"<#N/A")

2. If you're sending the workbook across countries...

=SUMIF(Range,"<"&NA())

marketingman wrote:
Rather than #N/A being returned from a lookup for a value that is not in the
lookup table can I replace the returned #N/A with a zero?

Without being able to do this I am unable sum the returned values.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Mr Mike Excel Worksheet Functions 4 May 25th 05 04:51 PM rtjeter Excel Worksheet Functions 2 April 26th 05 05:56 AM James Excel Worksheet Functions 2 April 6th 05 10:28 PM automne Excel Discussion (Misc queries) 2 March 7th 05 12:38 AM jddtct Excel Worksheet Functions 3 January 11th 05 07:03 AM

All times are GMT +1. The time now is 05:54 AM.