Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
marketingman
 
Posts: n/a
Default 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   Report Post  
CLR
 
Posts: n/a
Default 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   Report Post  
mbarron
 
Posts: n/a
Default How do I replace a return value of #N/A in a vlookup with zero?

Try this:

If(isna(vlookup(your lookup)),0,vlookup(your lookup))

  #4   Report Post  
Ashish Mathur
 
Posts: n/a
Default 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   Report Post  
Aladin Akyurek
 
Posts: n/a
Default 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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Have Vlookup return a Value of 0 instead of #N/A Mr Mike Excel Worksheet Functions 4 May 25th 05 04:51 PM
Using a Vlookup to return values in a data list? rtjeter Excel Worksheet Functions 2 April 26th 05 05:56 AM
Vlookup of an if statement return James Excel Worksheet Functions 2 April 6th 05 10:28 PM
Vlookup - return row no. instead of value automne Excel Discussion (Misc queries) 2 March 7th 05 01:38 AM
Can VLOOKUP return multiple answers based on several identical lo. jddtct Excel Worksheet Functions 3 January 11th 05 08:03 AM


All times are GMT +1. The time now is 03:46 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"