Compare 3 columns not in a string if match

[michelles4731]

I need to compare compensation report. Columns I, M, and P all need to match in each row. What is the formula?

[TABLE="width: 800"]
<tbody>[TR]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]P[/TD]
[/TR]
[TR]
[TD="align: center"]Company Name[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Attainment[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Recurring Revenue[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Total Production Revenue[/TD]
[/TR]
[TR]
[TD]AAA Inc[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]102[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]102[/TD]
[TD][/TD]
[TD][/TD]
[TD]102[/TD]
[/TR]
[TR]
[TD]ABC Toys[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]185[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]370[/TD]
[TD][/TD]
[TD][/TD]
[TD]370[/TD]
[/TR]
[TR]
[TD]BC Cheese[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1095[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1095[/TD]
[TD][/TD]
[TD][/TD]
[TD]1095[/TD]
[/TR]
[TR]
[TD]Dog Days Inc.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]600[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1200[/TD]
[TD][/TD]
[TD][/TD]
[TD]1200[/TD]
[/TR]
[TR]
[TD]Produce to You[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]250[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]250[/TD]
[TD][/TD]
[TD][/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD]Sunkin Ships[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]375[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]750[/TD]
[TD][/TD]
[TD][/TD]
[TD]750[/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

You havent actually specified what you want listed, just that "they need to match".
If you want to list only those companies where columns I M and P all match then use this

in Q1
=IFERROR(INDEX($D$1:$D$6,SMALL(IF((I$1:I$6=M$1:M$6)*(I$1:I$6=P$1:P$6),ROW($A$1:$A$6)),ROW(A1))-(ROW(A$1)-1),1),"")
Array formula, use Ctrl-Shift-Enter
and copy down the column

 

[michelles4731]

Excellent point. I would like the formula to return an empty cell if all cells match and "doesn't match" in the rows that don't match.

 

[Special-K99]

in Q2
=IF(AND(I2=M2,I2=P2),"","Doesn't match")
and copy down the column

 

[michelles4731]

That worked perfectly. Thank you.