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!
|