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
1	Child	Parent	Child Contact	Parent Contact		Identify
2	123	123	John	John		FALSE
3	DEF	123	John	John		FALSE
4	345	345	Sam	Sam		TRUE
5	JKL	345	David	Sam		TRUE
6	MNO	345		Sam		FALSE
7	567	567				TRUE
8	TUV	567	Scott			TRUE
9	789	789	Kim	Kim		FALSE
10	QRS	789		Kim		FALSE

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet6​

Worksheet Formulas Cell Formula F2 =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))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>