Home

SYNTAX ISSUE

%3CLINGO-SUB%20id%3D%22lingo-sub-809115%22%20slang%3D%22en-US%22%3ESYNTAX%20ISSUE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-809115%22%20slang%3D%22en-US%22%3ESyntax%20help%20requested%20I%20have%20this%20formula%20%3DIF(AND(ISNA(MATCH(B5%2CSheet3!A5%3AA3349%2C0))%2CISNA(MATCH(%22*%22%26amp%3BA5%26amp%3B%22*%22%2CSheet3!D5%3AD3349%2C0)))%2C%22NO%22%2C%22YES%22)%20which%20I%20made%20by%20combining%20%3DIF(ISNA(MATCH(B2%2CSheet3!A2%3AA3346%2C0))%2C%22NO%22%2C%22YES%22)%20with%20%3DIF(ISNA(MATCH(%22*%22%26amp%3BA2%26amp%3B%22*%22%2CSheet3!D2%3AD3346%2C0))%2C%22NO%22%2C%22YES%22)%20I%20validated%20the%20results%20and%20individually%20they%20work%20just%20fine.%20Combined%20in%20an%20IFAND%20function%20the%20result%20is%20only%20NO%20if%20both%20MATCH%20functions%20generate%20%23N%2FA.%20If%20either%20one%20come%20ups%20%23N%2FA%20and%20the%20other%20does%20not%20or%20if%20both%20do%20not%20come%20up%20%23N%2FA%20the%20formula%20returns%20a%20%22YES%22%20I'm%20sure%20it's%20syntax%20error%20I'm%20just%20not%20seeing%20it.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-809115%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-809250%22%20slang%3D%22en-US%22%3ERe%3A%20SYNTAX%20ISSUE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-809250%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F393200%22%20target%3D%22_blank%22%3E%40zond2%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20fact%2C%20there%20is%20no%20error!%3C%2FP%3E%3CP%3EThis%20is%20how%20the%20logic%20in%20%3CA%20href%3D%22https%3A%2F%2Fwww.ablebits.com%2Foffice-addins-blog%2F2014%2F12%2F17%2Fexcel-and-or-xor-not-functions%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3EAND%20function%3C%2FA%3Eworks.%3C%2FP%3E%3CP%3EThe%20formula%20will%20return%20%22NO%22%20only%20if%20both%20matches%20return%20%3CSTRONG%3E%23N%2FA%3C%2FSTRONG%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20want%20the%20formula%20to%20return%20%22NO%22%20if%20at%20least%20one%20Match%20return%20%3CSTRONG%3E%23N%2FA%3C%2FSTRONG%3E%2C%20then%20you%20can%20use%20OR%20function%20instead%2C%20like%20the%20following%3A%3C%2FP%3E%3CPRE%3E%3DIF(OR(ISNA(MATCH(B5%2CSheet3!A5%3AA3349%2C0))%2CISNA(MATCH(%22*%22%26amp%3BA5%26amp%3B%22*%22%2CSheet3!D5%3AD3349%2C0)))%2C%22NO%22%2C%22YES%22)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E
zond2
New Contributor
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.
1 Reply

@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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
IIS extension is not working - WAC 1909
HotCakeX in Windows Admin Center on
11 Replies