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

1 Reply

  • 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 https://www.ablebits.com/office-addins-blog/2014/12/17/excel-and-or-xor-not-functions/ 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