Using Excel error #N/A in a formula

[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?

 

[Jason Morin]

=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")

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?

 

[Harlan Grove]

=IF(ISNA(MATCH(C1,Range,0)),"OK","STOP")
but it does not seem to work. . . .

An alternative that avoids the whole issue:

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

 

[Peo Sjoblom]

...

An alternative that avoids the whole issue:

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

--

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

 

[Harlan Grove]

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

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