Using Excel error #N/A in a formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to capture the error message #N/A in an IF statement

Would like to use IF(MATCH(C1,Range,0)=#N/A,"OK","STOP") but it does not seem to work. What is the correct syntax to capture the #N/A error message and display "OK" instead of #N/A?
 
=IF(ISNA(MATCH(C1,Range,0)),"OK","STOP")

HTH
Jason
Atlanta, GA
-----Original Message-----
I would like to capture the error message #N/A in an IF statement.

Would like to use IF(MATCH(C1,Range,0)=#N/A,"OK","STOP")
Click to expand...
but it does not seem to work. What is the correct syntax
to capture the #N/A error message and display "OK" instead
of #N/A?
 
=IF(ISNA(MATCH(C1,Range,0)),"OK","STOP")
but it does not seem to work. . . .
Click to expand...

An alternative that avoids the whole issue:

=IF(COUNTIF(Range,C1),"STOP","OK")
 
Harlan Grove said:
...

An alternative that avoids the whole issue:

=IF(COUNTIF(Range,C1),"STOP","OK")

--
Click to expand...

The problem with countif (as I experienced personally)
is that it doesn't differ between text numbers and numbers. Obviously it
will work if
the lookup value is a text string however if it is a number it can return
true while the vlookup
returns #N/A and that could be confusing.
i.e.

=COUNTIF(Range,"1")

return the same as

=COUNTIF(Range,1)

while

=VLOOKUP("1",Table,2,0)

and

=VLOOKUP(1,Table,2,0)

does not
 
...
The problem with countif (as I experienced personally)
is that it doesn't differ between text numbers and numbers. Obviously it ...
=COUNTIF(Range,"1")

return the same as

=COUNTIF(Range,1)

while

=VLOOKUP("1",Table,2,0)

and

=VLOOKUP(1,Table,2,0)

does not
Click to expand...

Good point. An alternative would be SUMPRODUCT(--(Range=1)), which doesn't
suffer from this.
 
Back
Top