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:

	U	V
1	Name	Date
2	A	10/01/15
3	B	10/02/15
4	C	10/03/15
5	D	10/01/15
6	E	10/04/15
7	F	10/04/15
8	G	10/05/15
9	H	10/05/15
10	I	10/06/15
11	J	10/06/15
12	K	10/06/15

<tbody>
</tbody>

	A	B	C
1	Date	Name	Flag?
2	10/01/15	A	1
3	10/01/15	B	0
4	10/01/15	C	0
5	10/01/15	D	1
6	10/01/15	D	1
7	10/02/15	D	1
8	10/02/15	E	0
9	10/02/15	F	0
10	10/03/15	A	1
11	10/03/15	T	0
12	10/03/15	R	0
13	10/03/15	D	1
14	10/04/15	B	1
15	10/04/15	J	0
16	10/04/15	K	0
17	10/05/15	T	0
18	10/05/15	T	0
19	10/05/15	T	0
20	10/06/15	S	0
21	10/06/15	I	1
22	10/06/15	I	1
23	10/07/15	I	1
24	10/07/15	W	0
25	10/07/15	A	1
26	10/08/15	A	1
27	10/08/15	B	1
28	10/08/15	B	1
29	10/09/15	C	1
30	10/09/15	V	0

<tbody>
</tbody>

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