Forum Discussion

zond2's avatar
zond2
Copper Contributor
Aug 16, 2019

SYNTAX ISSUE

Syntax help requested I have this formula =IF(AND(ISNA(MATCH(B5,Sheet3!A5:A3349,0)),ISNA(MATCH("*"&A5&"*",Sheet3!D5:D3349,0))),"NO","YES") which I made by combining =IF(ISNA(MATCH(B2,Sheet3!A2:A3346,0)),"NO","YES") with =IF(ISNA(MATCH("*"&A2&"*",Sheet3!D2:D3346,0)),"NO","YES") I validated the results and individually they work just fine. Combined in an IFAND function the result is only NO if both MATCH functions generate #N/A. If either one come ups #N/A and the other does not or if both do not come up #N/A the formula returns a "YES" I'm sure it's syntax error I'm just not seeing it.
Formulas and Functions
  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Aug 17, 2019

    zond2

     

    Hi,

     

    In fact, there is no error!

    This is how the logic in AND function works.

    The formula will return "NO" only if both matches return #N/A.

     

    If you want the formula to return "NO" if at least one Match return #N/A, then you can use OR function instead, like the following:

    =IF(OR(ISNA(MATCH(B5,Sheet3!A5:A3349,0)),ISNA(MATCH("*"&A5&"*",Sheet3!D5:D3349,0))),"NO","YES")

     

    Hope that helps

Resources