Excel formula compare a table

Question

I'm trying to compare a row Table A to Table B but value have different column. I need formula for if they are match or not.

I already tried Match or IF but criteria range are applicable to one cell.

Example

Answer 1

You can use this formula:

=IF(OR(A2=E2,A2=F2,A2,G2,B2=E2,B2=F2,B2=G2,C2=E2,C2=F2,C2=G2),"YES","NO")

Put it to your match column and drag it down.

Answer 2

There are multiple options, here is an array example:

=IF(SUMPRODUCT(--(A2:C2=TRANPOSE(E2:G2)))=3,"Yes","No")

Confirm through CtrlShiftEnter

A simple non-array version would be to just add multiple countif:

=IF(COUNTIF(A2:C2,E2)+COUNTIF(A2:C2,F2)+COUNTIF(A2:C2,G2)=3,"Yes","No")

Or a combination with AND:

=IF(AND(COUNTIF(A2:C2,E2),COUNTIF(A2:C2,F2),COUNTIF(A2:C2,G2)),"Yes","No")

Answer 3

Maybe something like that:

=ISNUMBER(MATCH(TableB[@Header4],TableA[@],0)*MATCH(TableB[@Header5],TableA[@],0)*MATCH(TableB[@Header6],TableA[@],0))

Translation:

• Match cell E2 with the first row of TableA, multiplied by:
• Match cell F2 with the first row of TableA, multiplied by:
• Match cell G2 with the first row of TableA

If any of these three functions fails, the whole formula returns #N/A (that is why we need to check it by using ISNUMBER).

Hope it works for you.