Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 13th 05, 03:26 PM
dave
 
Posts: n/a
Default vlookup gives me a #N/A

When I try to look with vlookup for a value that is not in the table
indicated, I get the message #N/A which indicates that the value cannot be
found (this is normal).

I would like to change this message #N/A into a value 0 or a blanc ""
because you cannot calculate with this value.

Is this possible??

  #2   Report Post  
Old October 13th 05, 03:36 PM
arno
 
Posts: n/a
Default

Is this possible??

only with something like:

=if(iserror(your vlookup);"";your vlookup)

where "your vlookup" is the formula you are currently using.

arno
  #3   Report Post  
Old October 13th 05, 03:37 PM
Richard Buttrey
 
Posts: n/a
Default

On Thu, 13 Oct 2005 07:26:01 -0700, "dave"
wrote:

When I try to look with vlookup for a value that is not in the table
indicated, I get the message #N/A which indicates that the value cannot be
found (this is normal).

I would like to change this message #N/A into a value 0 or a blanc ""
because you cannot calculate with this value.

Is this possible??


Just wrap your vlookup in an If statement. i.e.

If(isna(vlookup(blah_blah)),0,vlookup(blah_blah))

Rgds
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
  #4   Report Post  
Old October 13th 05, 08:21 PM
Aladin Akyurek
 
Posts: n/a
Default

If you have this VLOOKUP formula, say, in 1000 or more cells, it might
be better to restort to formulas like:

=SUMIF(Range,"<#N/A") for summing;

{=AVERAGE(IF(ISNUMBER(Range),Range,""))} for averaging;

and comparable formulas for other statistics than trying to suppress
#N/A's by formulas that compute the same thing twice like:

=IF(ISNA(VLOOKUP(...)),"",VLOOKUP(...)).

dave wrote:
When I try to look with vlookup for a value that is not in the table
indicated, I get the message #N/A which indicates that the value cannot be
found (this is normal).

I would like to change this message #N/A into a value 0 or a blanc ""
because you cannot calculate with this value.

Is this possible??


--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.
  #5   Report Post  
Old October 13th 05, 11:34 PM
Bob Phillips
 
Posts: n/a
Default

=IF(ISNA(vlookup_formula),"",vlookup_formula)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"dave" wrote in message
...
When I try to look with vlookup for a value that is not in the table
indicated, I get the message #N/A which indicates that the value cannot be
found (this is normal).

I would like to change this message #N/A into a value 0 or a blanc ""
because you cannot calculate with this value.

Is this possible??





  #6   Report Post  
Old December 11th 05, 02:09 AM posted to microsoft.public.excel.worksheet.functions
Alamo
 
Posts: n/a
Default vlookup gives me a #N/A

Excel automatically corrects this formula with 3 ))) at the end of the formula.

In my case it is:
=IF(ISNA(VLOOKUP(B44,itemlist!$A16:$B126,2)),0,(VL OOKUP(B44,itemlist!$A16:$B126,2)))

I do not want the 0 to show up on the form - how can it be blank?

Mike Boehmer
San Antonio, TX
Home of the NBA Champs - The SPURS-



"Richard Buttrey" wrote:

On Thu, 13 Oct 2005 07:26:01 -0700, "dave"
wrote:

When I try to look with vlookup for a value that is not in the table
indicated, I get the message #N/A which indicates that the value cannot be
found (this is normal).

I would like to change this message #N/A into a value 0 or a blanc ""
because you cannot calculate with this value.

Is this possible??


Just wrap your vlookup in an If statement. i.e.

If(isna(vlookup(blah_blah)),0,vlookup(blah_blah))

Rgds
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

  #7   Report Post  
Old December 11th 05, 03:44 AM posted to microsoft.public.excel.worksheet.functions
Dave Peterson
 
Posts: n/a
Default vlookup gives me a #N/A

=if(isna(vlookup(...)),"",vlookup(...))



Alamo wrote:

Excel automatically corrects this formula with 3 ))) at the end of the formula.

In my case it is:
=IF(ISNA(VLOOKUP(B44,itemlist!$A16:$B126,2)),0,(VL OOKUP(B44,itemlist!$A16:$B126,2)))

I do not want the 0 to show up on the form - how can it be blank?

Mike Boehmer
San Antonio, TX
Home of the NBA Champs - The SPURS-

"Richard Buttrey" wrote:

On Thu, 13 Oct 2005 07:26:01 -0700, "dave"
wrote:

When I try to look with vlookup for a value that is not in the table
indicated, I get the message #N/A which indicates that the value cannot be
found (this is normal).

I would like to change this message #N/A into a value 0 or a blanc ""
because you cannot calculate with this value.

Is this possible??


Just wrap your vlookup in an If statement. i.e.

If(isna(vlookup(blah_blah)),0,vlookup(blah_blah))

Rgds
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________


--

Dave Peterson


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
Using single cell reference as table array argument in Vlookup CornNiblet Excel Worksheet Functions 3 September 22nd 05 09:15 AM
VLOOKUP Limitations chris_manning Excel Worksheet Functions 2 August 9th 05 06:23 PM
Have Vlookup return a Value of 0 instead of #N/A Mr Mike Excel Worksheet Functions 4 May 25th 05 04:51 PM
vlookup data hidden within worksheet Excel Worksheet Functions 0 January 26th 05 01:09 PM
Vlookup info being used without vlookup table attached? Excel Worksheet Functions 0 January 25th 05 11:43 AM


All times are GMT +1. The time now is 07:38 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017