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?? |
Is this possible??
only with something like: =if(iserror(your vlookup);"";your vlookup) where "your vlookup" is the formula you are currently using. arno |
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 __________________________ |
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. |
=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?? |
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 __________________________ |
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 |
All times are GMT +1. The time now is 12:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com