Flagging if cell found within list then checking if date is greater than that in list

[staprila]

Hi all,

I'm having a tough time flagging rows that match the values within a list and also have dates that are greater or equal to those dates that correspond to the names in the list. See example below:

List:

[TABLE="width: 400, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]U[/TD]
[TD]V[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD]10/01/15[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]B[/TD]
[TD]10/02/15[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]C[/TD]
[TD]10/03/15[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]D[/TD]
[TD]10/01/15[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]E[/TD]
[TD]10/04/15[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]F[/TD]
[TD]10/04/15[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]G[/TD]
[TD]10/05/15[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]H[/TD]
[TD]10/05/15[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]I[/TD]
[TD]10/06/15[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]J[/TD]
[TD]10/06/15[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]K[/TD]
[TD]10/06/15[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date[/TD]
[TD]Name[/TD]
[TD]Flag?[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]10/01/15[/TD]
[TD]A[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]10/01/15[/TD]
[TD]B[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]10/01/15[/TD]
[TD]C[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]10/01/15[/TD]
[TD]D[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]10/01/15[/TD]
[TD]D[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]10/02/15[/TD]
[TD]D[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]10/02/15[/TD]
[TD]E[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]10/02/15[/TD]
[TD]F[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]10/03/15[/TD]
[TD]A[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]10/03/15[/TD]
[TD]T[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]10/03/15[/TD]
[TD]R[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]10/03/15[/TD]
[TD]D[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]10/04/15[/TD]
[TD]B[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]10/04/15[/TD]
[TD]J[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]10/04/15[/TD]
[TD]K[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]10/05/15[/TD]
[TD]T[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]10/05/15[/TD]
[TD]T[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]10/05/15[/TD]
[TD]T[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]10/06/15[/TD]
[TD]S[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]10/06/15[/TD]
[TD]I[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]10/06/15[/TD]
[TD]I[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]10/07/15[/TD]
[TD]I[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]10/07/15[/TD]
[TD]W[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]10/07/15[/TD]
[TD]A[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]10/08/15[/TD]
[TD]A[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]10/08/15[/TD]
[TD]B[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]10/08/15[/TD]
[TD]B[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD]10/09/15[/TD]
[TD]C[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]10/09/15[/TD]
[TD]V[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


What I am trying to produce is Column C. I have a feeling that the use of OFFSET will be involved but I haven't quite gotten the flag to work. Any help would be greatly appreciated.

Thank you!

 

[etaf]

a countifs may work

i assume the columns in the list are U and V

=countifs ( u:u, b2, v:v , a2)
now when it finds a match it will count 1 , if it finds more 2, 3 etc

so you could use an IF
=IF( countifs ( u:u, b2, v:v , a2) >0 , 1, 0 )

thats for equal to date

to do greater than
=IF( countifs ( u:u, b2, v:v , ">="&a2) >0 , 1, 0 )

 

[staprila]

Thank you, that worked very well!

 

[etaf]

you are welcome