If cell match in one column then check for match among 2 other different columns

[jr1977]

Hi,
I have a problem that I'm having difficulty solving. Please help.

[TABLE="width: 667"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD="align: right"]1 [/TD]
[TD]Code Name[/TD]
[TD]FirstnameProfile[/TD]
[TD]LastnameProfile[/TD]
[TD]FirstnameonBill[/TD]
[TD]LastnameonBill[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD] max[/TD]
[TD]Bob[/TD]
[TD]Johnson[/TD]
[TD]Robert[/TD]
[TD]Johnson[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD] max[/TD]
[TD]Bob[/TD]
[TD]Johnson[/TD]
[TD]Bob[/TD]
[TD]Johnson[/TD]
[/TR]
[TR]
[TD="align: right"]4 [/TD]
[TD]max[/TD]
[TD]Betty[/TD]
[TD]Johnson[/TD]
[TD]Bob&Betty[/TD]
[TD]Johnson[/TD]
[/TR]
[TR]
[TD="align: right"]5 [/TD]
[TD]royal[/TD]
[TD]Bobby[/TD]
[TD]Piper[/TD]
[TD]Bob[/TD]
[TD]Piper[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD] parrot123[/TD]
[TD]Jimmy[/TD]
[TD]Bear[/TD]
[TD]Jimmy[/TD]
[TD]Bear[/TD]
[/TR]
[TR]
[TD="align: right"]7 [/TD]
[TD]boots[/TD]
[TD]Paulie[/TD]
[TD]Roland[/TD]
[TD]Paul[/TD]
[TD]Roland[/TD]
[/TR]
[TR]
[TD="align: right"]8 [/TD]
[TD]boots[/TD]
[TD]Jenny[/TD]
[TD]Lester[/TD]
[TD]Paul[/TD]
[TD]Roland[/TD]
[/TR]
</tbody>[/TABLE]

What I need to do is look at the Code Names column. If any of these match, they are the subject of further investigation. For example, rows 2,3,& 4 are all code name "max". Next I need to see if the FirstnameProfile matches the FirstnameonBill for any of the "max" group. If they match it can print a YES in column F. If no match then No in column F.

For row 2 it would return a Yes because Bob (B2) matches Bob (D3).
For row 3 it would return a Yes because Bob (B3) matches Bob (D3).
For row 4 it would return a No because Betty (B4) does not match any of the First names from D2, D3 or D4.
For row 5 it would return a No because Bobby (B5) does not match D5
For row 6 it would return a Yes because Jimmy matches D6
For row 7 it would return a No because Paulie does not match D7 or D8
For row 8 it would return a No because Jenny does not match D7 or D8

thanks!

 

[Trouttrap2]

Understood. I was looking to add in matches to things Paulie and Paul. thanks again. Where in the formula is it looking at column D? Is that the "D" and ":D"? You're a lifesaver thanks!

Yes, the "D" and ":D" are expandable ranges.