View Single Post
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Peo Sjoblom Peo Sjoblom is offline
external usenet poster
 
Posts: 3,268
Default 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!