Using IF, AND, MATCH to cross check two related values is not working as intended

[nilnilnil0911]

So I'm trying to match two values against two columns in another table. It has to be a combination of both or it should return false.

For example, comparing Table1 and Table 2, they both have the same columns Color and Fruit. I want to find if a Blue, Apple in Table1 also exists in Table2 in the same row.

I came up with IF, AND, MATCH. I even checked it with ChatGPT and it just wouldn't work. Please help out this Excel noob.

=IF(AND(MATCH(B2; 'contacts'!A2:A1505;1)),(MATCH(D2; 'contacts'!B2:B1505;1)); "Yes"; "No")

 

[Fluff]

Hi & welcome to MrExcel.
How about

=IF(COUNTIFS(Contacts!A2:A1505,B2,Contacts!B2:B1505,D2), "Yes", "No")

 

[nilnilnil0911]

Hi & welcome to MrExcel.
How about

=IF(COUNTIFS(Contacts!A2:A1505,B2,Contacts!B2:B1505,D2), "Yes", "No")

Still an error. Is it a microsoft 365 problem? i cant seem to make it work.

 

[Fluff]

What error?

 

[nilnilnil0911]

What error?

It just wont go through, 365 doesnt let me enter it. BTW does your formula check the two values separately, I need them to be together in the same row. I tried this formula and returns an error as well (sigh)
=IFERROR(IF(MATCH(1,(B2=contacts!A2:A1505)*(D2=contacts!B2:B1505),0)=MATCH(1,(B2=contacts!A2:A1505)*(D2=contacts!B2:B1505),0),"Yes","No"),"No")

 

[Joe4]

Judging from your initial post, I am guessing maybe your version of Excel requires semi-colons instead of commas to separate formula arguments.
So you may need to replace all commas in Fluff's formula to semi-colons.

 

[Fluff]

BTW does your formula check the two values separately

No, they must be on the same row.