Look up value and display result from another column with multiple entries

[lolly150]

Column A is Client ID
Column B is referral date

Column C is Client ID
Column D is appt date
Column E is team name

I need help with a formula that looks up the client ID in column A against the Client ID in Column C and if it matches, display the result of the team name from Column E into column F. There can be more than one result

NB: client ID can be entered more than once in both Columns A and C because a client can be referred more than once to more than one team.

Any help gratefully received!

 

[oldbrewer]

copy A1 to H10 into reply box please

 

[lolly150]

[TABLE="width: 420"]
<tbody>[TR]
[TD]Client ID[/TD]
[TD]Referral Date[/TD]
[TD]Client ID[/TD]
[TD]Appt[/TD]
[TD]Team[/TD]
[/TR]
[TR]
[TD="align: right"]1777475[/TD]
[TD="align: right"]04/10/16[/TD]
[TD="align: right"]1777206[/TD]
[TD="align: right"]25/04/16[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD="align: right"]1777206[/TD]
[TD="align: right"]21/04/16[/TD]
[TD="align: right"]1777206[/TD]
[TD="align: right"]25/04/16[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="align: right"]11113233[/TD]
[TD="align: right"]08/07/16[/TD]
[TD="align: right"]1777206[/TD]
[TD="align: right"]11/10/16[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="align: right"]11115637[/TD]
[TD="align: right"]03/05/16[/TD]
[TD="align: right"]1777206[/TD]
[TD="align: right"]17/06/16[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="align: right"]11116168[/TD]
[TD="align: right"]14/06/16[/TD]
[TD="align: right"]1777206[/TD]
[TD="align: right"]24/10/16[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD="align: right"]1771741[/TD]
[TD="align: right"]15/11/16[/TD]
[TD="align: right"]1777206[/TD]
[TD="align: right"]28/06/16[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD="align: right"]11126567[/TD]
[TD="align: right"]18/11/16[/TD]
[TD="align: right"]1777206[/TD]
[TD="align: right"]17/10/16[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD="align: right"]11132335[/TD]
[TD="align: right"]22/03/17[/TD]
[TD="align: right"]1777475[/TD]
[TD="align: right"]13/02/17[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD="align: right"]111331181[/TD]
[TD="align: right"]08/03/17[/TD]
[TD="align: right"]1777475[/TD]
[TD="align: right"]10/03/17[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="align: right"]11133157[/TD]
[TD="align: right"]08/03/17[/TD]
[TD="align: right"]1777475[/TD]
[TD="align: right"]21/03/17[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: right"]111385511[/TD]
[TD="align: right"]19/07/16[/TD]
[TD="align: right"]1777081[/TD]
[TD="align: right"]18/10/16[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="align: right"]111331161[/TD]
[TD="align: right"]12/12/16[/TD]
[TD="align: right"]1771741[/TD]
[TD="align: right"]01/12/16[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD="align: right"]111331161[/TD]
[TD="align: right"]06/07/16[/TD]
[TD="align: right"]1771741[/TD]
[TD="align: right"]20/12/16[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD="align: right"]11142212[/TD]
[TD="align: right"]16/02/17[/TD]
[TD="align: right"]1771167[/TD]
[TD="align: right"]04/07/16[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="align: right"]11146686[/TD]
[TD="align: right"]19/04/16[/TD]
[TD="align: right"]1771165[/TD]
[TD="align: right"]18/04/16[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="align: right"]111471132[/TD]
[TD="align: right"]07/06/16[/TD]
[TD="align: right"]1771165[/TD]
[TD="align: right"]25/04/16[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="align: right"]11152537[/TD]
[TD="align: right"]08/03/17[/TD]
[TD="align: right"]1771165[/TD]
[TD="align: right"]04/05/16[/TD]
[TD]D[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col></colgroup>[/TABLE]