Need help with ISNA function

[Gadzooky]

Hi there,

First time poster here. I'm trying to insert a formula that will check off a PN against four different PN tables and return either "YES" or "NO" depending on whether the PN is present in one of the four reference tables, or it's a brand new PN.

The formula I tried is =IF((MATCH(A67,B67:F67,0))<>"INSA","NO","YES")

This works no problem if the PN is already found, but instead of returning "YES" if the PN is new, it returns #N/A.

Clearly I'm not using the INSA function in the correct manner, can someone please help?

Thanks!

[FDibbins]

Hi and welcome to the forum

Maybe this?

=IF(iserror(MATCH(A67,B67:F67,0)),"NO","YES")

[daddylonglegs]

You can use ISNUMBER or ISNA functions like this

=IF(ISNUMBER(MATCH(A67,B67:F67,0)),"YES","NO")

or reversed

=IF(ISNA(MATCH(A67,B67:F67,0)),"NO","YES")

[AliGW]

ISNA is a function. Try:

=IF(ISNA(MATCH(A67,B67:F67,0)),"YES","NO")

[AliGW]

You can use ISNUMBER or ISNA functions like this

=IF(ISNUMBER(MATCH(A67,B67:F67,0)),"YES","NO")

or reversed

=IF(ISNA(MATCH(A67,B67:F67,0)),"NO","YES")

Won't this return "NO" if the PN is new???

[daddylonglegs]

Won't this return "NO" if the PN is new???

I probably misread - Yes, I think my "Yes" and "No" need to be reversed

[daddylonglegs]

....perhaps COUNTIF is simpler, assuming you want "Yes" when the A67 value doesn't exist in B67:F67

=IF(COUNTIF(B67:F67,A67),"No","Yes")

[Gadzooky]

Works perfectly now - thank you very much for the quick response!

[AliGW]

You are welcome!