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
non-numeric/non-existant, 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!

Answer: NZ function equivalent in Excel
 

Yes, Excel has an equivalent function to Access' NZ function. The function is called IFERROR.

The IFERROR function allows you to specify a value to return if a formula returns an error. This can be useful in situations where you want to display a specific value instead of an error message.

Here's an example of how you can use the IFERROR function to replace your current formula:

1. =IFERROR(VLOOKUP(A1,M1:N30,2,FALSE),0)

This formula will return the value from the VLOOKUP function if it is successful, and will return 0 if the VLOOKUP function returns an error.

You can also use the IFERROR function with other formulas that may return errors, such as division by zero errors or #VALUE! errors.

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
non-numeric/non-existant, 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!

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
non-numeric/non-existant, 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!

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
non-numeric/non-existant, 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!

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
non-numeric/non-existant, 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!

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
non-numeric/non-existant, 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!

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
non-numeric/non-existant, 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!