Matrix style Vlookup?

[csh8428]

I have a spreadsheet with 4 columns: A - Child, B - Parent, C - Child contact, D - Parent contact
Each Parent belongs to the same parent(this is needed for reasons unrelated to this issue)
Each child belongs to a parent
Each child can have none, 1, or multiple contacts. Each Parent can have none, 1, or multiple contacts
These contacts may be the same for the child and the parent.

What I need to know is to identify each parent and child where the child and parent have a different contact, unless the parent has a contact, but the child doesn't.

|  Child | Parent | Child contact | Parent Contact|
| 123 |   123      |   John            |    John            |
| DEF  |   123    |   John            |   John             |
| 345  |   345     |   Sam            |   Sam             |
| JKL   |   345    | David            |   Sam             |
| MNO |  345     |                     |   Sam             | 
| 567  |   567     |                     |                       |
| TUV  |  567     | Scott            |                       |
| 789  |   789     | Kim              |  Kim                |
| QRS |  789      |                    | Kim                 |

These SHOULD be identified
345(Parent) - Parent has a different contact than at least one of the children
JKL (child) - has different contact than it's parent(345)
567(Parent) - has different contact than child
TUV(child) has different contact than parent

These should NOT be identified
123(parent), DEF(child) - same contacts
789(parent), QRS(child) - Child doesn't have a contact, but the parent does
MNO(child) - child doesn't have a contact, but the parent does

Thanks for any help!

Craig

 

[Eric W]

It took me a while to work through your logic, but I believe this does what you want:

	A	B	C	D	E	F
Child	Parent	Child Contact	Parent Contact	Identify
John	John
DEF	John	John
Sam	Sam
JKL	David	Sam
MNO	Sam
TUV	Scott
Kim	Kim
QRS	Kim

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]123[/TD]

[TD="align: right"][/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]123[/TD]

[TD="align: right"][/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]345[/TD]
[TD="align: right"]345[/TD]

[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]345[/TD]

[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]345[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]567[/TD]
[TD="align: right"]567[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]567[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]789[/TD]
[TD="align: right"]789[/TD]

[TD="align: right"][/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]789[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]FALSE[/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=IF(A2=B2,COUNTIFS(B:B,B2,C:C,D2)+COUNTIFS(B:B,B2,C:C,"")< COUNTIF(B:B,B2),IF(C2<>"",IF(COUNTIFS(A:A,B2,D:D,C2)=0,TRUE)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]