Home 
Search 
Today's Posts 
#1




NZ function equivalent in Excel
Does Excel have an equivalent to the Access function NZ? I want to avoid
having to perform a double VLOOKUP (one to determine if the value is nonnumeric/nonexistant, the second to pull the actual value in the event it is numeric). E.g: =IF(ISERROR(VLOOKUP(A1,M1:N30,2,FALSE)),0,VLOOKUP( A1,M1:N30,2,FALSE)) I've written a custom function to address it, but would rather not have users with high security levels blocked from using the function. For reference, the function I built is here (yes, it's not an exact match...but it does what I need for now): <snip Public Function NZ(CellVal As Variant) As Variant Select Case VarType(CellVal) Case vbError NZ = 0 Case vbInteger, vbLong, vbDouble, vbSingle, vbCurrency, vbDecimal, vbByte NZ = CellVal Case Else NZ = "#WRONG_TYPE" End Select End Function </snip ....such that the equivalent to the above is: =NZ(VLOOKUP(A1,M1:N30,2,FALSE)) Thanks! 
#2




NZ function equivalent in Excel
Unless you're using Excel 2007 you're likely out of luck.
In 2007 there is a new IFERROR(value1,value2) function that returns value1 if it is not an error, otherwise returns value2 "jg" wrote: Does Excel have an equivalent to the Access function NZ? I want to avoid having to perform a double VLOOKUP (one to determine if the value is nonnumeric/nonexistant, the second to pull the actual value in the event it is numeric). E.g: =IF(ISERROR(VLOOKUP(A1,M1:N30,2,FALSE)),0,VLOOKUP( A1,M1:N30,2,FALSE)) I've written a custom function to address it, but would rather not have users with high security levels blocked from using the function. For reference, the function I built is here (yes, it's not an exact match...but it does what I need for now): <snip Public Function NZ(CellVal As Variant) As Variant Select Case VarType(CellVal) Case vbError NZ = 0 Case vbInteger, vbLong, vbDouble, vbSingle, vbCurrency, vbDecimal, vbByte NZ = CellVal Case Else NZ = "#WRONG_TYPE" End Select End Function </snip ...such that the equivalent to the above is: =NZ(VLOOKUP(A1,M1:N30,2,FALSE)) Thanks! 
#3




NZ function equivalent in Excel
Excel 2007 has a new function called IFERROR but not earlier versions
IFERROR(VLOOKUP(),0) So you are stuck with either a UDF or =IF(ISNUMBER(MATCH(A1,M1:M30,0)),VLOOKUP(A1,M1:N30 ,2,FALSE),0) or =IF(ISNA(VLOOKUP(A1,M1:N30,2,FALSE)),0,VLOOKUP(A1, M1:N30,2,FALSE))  Regards, Peo Sjoblom "jg" wrote in message ... Does Excel have an equivalent to the Access function NZ? I want to avoid having to perform a double VLOOKUP (one to determine if the value is nonnumeric/nonexistant, the second to pull the actual value in the event it is numeric). E.g: =IF(ISERROR(VLOOKUP(A1,M1:N30,2,FALSE)),0,VLOOKUP( A1,M1:N30,2,FALSE)) I've written a custom function to address it, but would rather not have users with high security levels blocked from using the function. For reference, the function I built is here (yes, it's not an exact match...but it does what I need for now): <snip Public Function NZ(CellVal As Variant) As Variant Select Case VarType(CellVal) Case vbError NZ = 0 Case vbInteger, vbLong, vbDouble, vbSingle, vbCurrency, vbDecimal, vbByte NZ = CellVal Case Else NZ = "#WRONG_TYPE" End Select End Function </snip ...such that the equivalent to the above is: =NZ(VLOOKUP(A1,M1:N30,2,FALSE)) Thanks! 
#4




NZ function equivalent in Excel
Bummer news, but I appreciate the response. Thanks Duke!
"Duke Carey" wrote: Unless you're using Excel 2007 you're likely out of luck. In 2007 there is a new IFERROR(value1,value2) function that returns value1 if it is not an error, otherwise returns value2 "jg" wrote: Does Excel have an equivalent to the Access function NZ? I want to avoid having to perform a double VLOOKUP (one to determine if the value is nonnumeric/nonexistant, the second to pull the actual value in the event it is numeric). E.g: =IF(ISERROR(VLOOKUP(A1,M1:N30,2,FALSE)),0,VLOOKUP( A1,M1:N30,2,FALSE)) I've written a custom function to address it, but would rather not have users with high security levels blocked from using the function. For reference, the function I built is here (yes, it's not an exact match...but it does what I need for now): <snip Public Function NZ(CellVal As Variant) As Variant Select Case VarType(CellVal) Case vbError NZ = 0 Case vbInteger, vbLong, vbDouble, vbSingle, vbCurrency, vbDecimal, vbByte NZ = CellVal Case Else NZ = "#WRONG_TYPE" End Select End Function </snip ...such that the equivalent to the above is: =NZ(VLOOKUP(A1,M1:N30,2,FALSE)) Thanks! 
#5




NZ function equivalent in Excel
Try this:
=IF(COUNTIF(M1:M30,A1),VLOOKUP(A1,M1:N30,2,0),0) "jg" wrote: Does Excel have an equivalent to the Access function NZ? I want to avoid having to perform a double VLOOKUP (one to determine if the value is nonnumeric/nonexistant, the second to pull the actual value in the event it is numeric). E.g: =IF(ISERROR(VLOOKUP(A1,M1:N30,2,FALSE)),0,VLOOKUP( A1,M1:N30,2,FALSE)) I've written a custom function to address it, but would rather not have users with high security levels blocked from using the function. For reference, the function I built is here (yes, it's not an exact match...but it does what I need for now): <snip Public Function NZ(CellVal As Variant) As Variant Select Case VarType(CellVal) Case vbError NZ = 0 Case vbInteger, vbLong, vbDouble, vbSingle, vbCurrency, vbDecimal, vbByte NZ = CellVal Case Else NZ = "#WRONG_TYPE" End Select End Function </snip ...such that the equivalent to the above is: =NZ(VLOOKUP(A1,M1:N30,2,FALSE)) Thanks! 
#6




NZ function equivalent in Excel
If I'm not mistaken, this is better than what I had started with, but still
requires both a table scan and a separate aggregation. However, given my version limitation (Office 2003), may be the best solution short of keeping the custom formula. Thanks Mama! jg "Teethless mama" wrote: Try this: =IF(COUNTIF(M1:M30,A1),VLOOKUP(A1,M1:N30,2,0),0) "jg" wrote: Does Excel have an equivalent to the Access function NZ? I want to avoid having to perform a double VLOOKUP (one to determine if the value is nonnumeric/nonexistant, the second to pull the actual value in the event it is numeric). E.g: =IF(ISERROR(VLOOKUP(A1,M1:N30,2,FALSE)),0,VLOOKUP( A1,M1:N30,2,FALSE)) I've written a custom function to address it, but would rather not have users with high security levels blocked from using the function. For reference, the function I built is here (yes, it's not an exact match...but it does what I need for now): <snip Public Function NZ(CellVal As Variant) As Variant Select Case VarType(CellVal) Case vbError NZ = 0 Case vbInteger, vbLong, vbDouble, vbSingle, vbCurrency, vbDecimal, vbByte NZ = CellVal Case Else NZ = "#WRONG_TYPE" End Select End Function </snip ...such that the equivalent to the above is: =NZ(VLOOKUP(A1,M1:N30,2,FALSE)) Thanks! 
#7




NZ function equivalent in Excel
Note that COUNTIF does not make any difference between text numbers and
"real" numbers whereas MATCH and VLOOKUP do, so you can still get the #N/A! error after passing the countif test.  Regards, Peo Sjoblom "jg" wrote in message ... If I'm not mistaken, this is better than what I had started with, but still requires both a table scan and a separate aggregation. However, given my version limitation (Office 2003), may be the best solution short of keeping the custom formula. Thanks Mama! jg "Teethless mama" wrote: Try this: =IF(COUNTIF(M1:M30,A1),VLOOKUP(A1,M1:N30,2,0),0) "jg" wrote: Does Excel have an equivalent to the Access function NZ? I want to avoid having to perform a double VLOOKUP (one to determine if the value is nonnumeric/nonexistant, the second to pull the actual value in the event it is numeric). E.g: =IF(ISERROR(VLOOKUP(A1,M1:N30,2,FALSE)),0,VLOOKUP( A1,M1:N30,2,FALSE)) I've written a custom function to address it, but would rather not have users with high security levels blocked from using the function. For reference, the function I built is here (yes, it's not an exact match...but it does what I need for now): <snip Public Function NZ(CellVal As Variant) As Variant Select Case VarType(CellVal) Case vbError NZ = 0 Case vbInteger, vbLong, vbDouble, vbSingle, vbCurrency, vbDecimal, vbByte NZ = CellVal Case Else NZ = "#WRONG_TYPE" End Select End Function </snip ...such that the equivalent to the above is: =NZ(VLOOKUP(A1,M1:N30,2,FALSE)) Thanks! 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Is there an Excel 2003 equivalent to Word's "versions" function?  Excel Discussion (Misc queries)  
Looking for the equivalent of a Maxif function  Excel Discussion (Misc queries)  
WORKDAY() Function Equivalent with SUMPRODUCT()  Excel Worksheet Functions  
"MAXIF" Equivalent function in Excel  Excel Worksheet Functions  
What is the Excel equivalent of the CELL function?  Excel Worksheet Functions 