Lookup a name present between StartDate and EndDate in other sheet

[reachsuresh87]

Hi,
I need some Excel expert guidance on achieving a requirement as mentioned in attached sheet and also explained below:

From C5 to N8 in 'Required Format', for every user in ‘Required Format sheet’, we need to check whether the corresponding user in exists in ‘Usage’ sheet with dates between StartDate and EndDate.

I entered Yes or No from C5 to N8 for your reference




[TABLE="width: 1046"]
<colgroup><col><col><col><col span="11"></colgroup><tbody>[TR]
[TD] [/TD]
[TD]Week[/TD]
[TD="align: right"]201710[/TD]
[TD="align: right"]201711[/TD]
[TD="align: right"]201712[/TD]
[TD="align: right"]201713[/TD]
[TD="align: right"]201714[/TD]
[TD="align: right"]201715[/TD]
[TD="align: right"]201716[/TD]
[TD="align: right"]201717[/TD]
[TD="align: right"]201718[/TD]
[TD="align: right"]201719[/TD]
[TD="align: right"]201720[/TD]
[TD="align: right"]201721[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]StartDate[/TD]
[TD="align: right"]02/13/17[/TD]
[TD="align: right"]02/20/17[/TD]
[TD="align: right"]02/27/17[/TD]
[TD="align: right"]03/06/17[/TD]
[TD="align: right"]03/13/17[/TD]
[TD="align: right"]03/20/17[/TD]
[TD="align: right"]03/27/17[/TD]
[TD="align: right"]04/03/17[/TD]
[TD="align: right"]04/10/17[/TD]
[TD="align: right"]04/17/17[/TD]
[TD="align: right"]04/24/17[/TD]
[TD="align: right"]05/01/17[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]EndDate[/TD]
[TD="align: right"]03/12/17[/TD]
[TD="align: right"]03/19/17[/TD]
[TD="align: right"]03/26/17[/TD]
[TD="align: right"]04/02/17[/TD]
[TD="align: right"]04/09/17[/TD]
[TD="align: right"]04/16/17[/TD]
[TD="align: right"]04/23/17[/TD]
[TD="align: right"]04/30/17[/TD]
[TD="align: right"]05/07/17[/TD]
[TD="align: right"]05/14/17[/TD]
[TD="align: right"]05/21/17[/TD]
[TD="align: right"]05/28/17[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]AA1[/TD]
[TD]Check if name in A5 present in 'Usage' sheet with dates between StartDate and EndDate[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]AA2[/TD]
[TD]Check if name in A6 present in 'Usage' sheet with dates between StartDate and EndDate[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]AA3[/TD]
[TD]Check if name in A7 present in 'Usage' sheet with dates between StartDate and EndDate[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]AA4[/TD]
[TD]Check if name in A8 present in 'Usage' sheet with dates between StartDate and EndDate[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 225"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Day[/TD]
[/TR]
[TR]
[TD]AA2[/TD]
[TD]2/13/2017[/TD]
[/TR]
[TR]
[TD]AA2[/TD]
[TD]2/13/2017[/TD]
[/TR]
[TR]
[TD]AA2[/TD]
[TD]2/23/2017[/TD]
[/TR]
[TR]
[TD]AA2[/TD]
[TD]2/24/2017[/TD]
[/TR]
[TR]
[TD]AA2[/TD]
[TD]2/24/2017[/TD]
[/TR]
[TR]
[TD]AA2[/TD]
[TD]2/24/2017[/TD]
[/TR]
[TR]
[TD]AA3[/TD]
[TD]3/1/2017[/TD]
[/TR]
[TR]
[TD]AA3[/TD]
[TD]3/1/2017[/TD]
[/TR]
[TR]
[TD]AA2[/TD]
[TD]3/7/2017[/TD]
[/TR]
[TR]
[TD]AA2[/TD]
[TD]3/10/2017[/TD]
[/TR]
[TR]
[TD]AA1[/TD]
[TD]4/6/2017[/TD]
[/TR]
[TR]
[TD]AA1[/TD]
[TD]4/6/2017[/TD]
[/TR]
[TR]
[TD]AA4[/TD]
[TD]4/25/2017[/TD]
[/TR]
[TR]
[TD]AA4[/TD]
[TD]5/3/2017[/TD]
[/TR]
[TR]
[TD]AA3[/TD]
[TD]5/7/2017[/TD]
[/TR]
[TR]
[TD]AA3[/TD]
[TD]5/7/2017[/TD]
[/TR]
[TR]
[TD]AA3[/TD]
[TD]5/7/2017[/TD]
[/TR]
[TR]
[TD]AA3[/TD]
[TD]5/7/2017[/TD]
[/TR]
[TR]
[TD]AA1[/TD]
[TD]5/22/2017[/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott T]

AA1 is listed with dates of 4/6/17, 4/6/17, 5/22/17 but you have Yes for 4/10-5/7, 4/17-5/14, and 4/24/17-5/21 I assume this is a typo.

[TABLE="class: grid, width: 937"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]N
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]201710
[/TD]
[TD="align: right"]201711
[/TD]
[TD="align: right"]201712
[/TD]
[TD="align: right"]201713
[/TD]
[TD="align: right"]201714
[/TD]
[TD="align: right"]201715
[/TD]
[TD="align: right"]201716
[/TD]
[TD="align: right"]201717
[/TD]
[TD="align: right"]201718
[/TD]
[TD="align: right"]201719
[/TD]
[TD="align: right"]201720
[/TD]
[TD="align: right"]201721
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2/13/2017
[/TD]
[TD="align: right"]2/20/2017
[/TD]
[TD="align: right"]2/27/2017
[/TD]
[TD="align: right"]3/6/2017
[/TD]
[TD="align: right"]3/13/2017
[/TD]
[TD="align: right"]3/20/2017
[/TD]
[TD="align: right"]3/27/2017
[/TD]
[TD="align: right"]4/3/2017
[/TD]
[TD="align: right"]4/10/2017
[/TD]
[TD="align: right"]4/17/2017
[/TD]
[TD="align: right"]4/24/2017
[/TD]
[TD="align: right"]5/1/2017
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3/12/2017
[/TD]
[TD="align: right"]3/19/2017
[/TD]
[TD="align: right"]3/26/2017
[/TD]
[TD="align: right"]4/2/2017
[/TD]
[TD="align: right"]4/9/2017
[/TD]
[TD="align: right"]4/16/2017
[/TD]
[TD="align: right"]4/23/2017
[/TD]
[TD="align: right"]4/30/2017
[/TD]
[TD="align: right"]5/7/2017
[/TD]
[TD="align: right"]5/14/2017
[/TD]
[TD="align: right"]5/21/2017
[/TD]
[TD="align: right"]5/28/2017
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]AA1
[/TD]
[TD][/TD]
[TD]No
[/TD]
[TD]No
[/TD]
[TD]No
[/TD]
[TD]No
[/TD]
[TD]Yes
[/TD]
[TD]Yes
[/TD]
[TD]Yes
[/TD]
[TD]Yes
[/TD]
[TD]No
[/TD]
[TD]No
[/TD]
[TD]No
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]AA2
[/TD]
[TD][/TD]
[TD]Yes
[/TD]
[TD]Yes
[/TD]
[TD]Yes
[/TD]
[TD]Yes
[/TD]
[TD]No
[/TD]
[TD]No
[/TD]
[TD]No
[/TD]
[TD]No
[/TD]
[TD]No
[/TD]
[TD]No
[/TD]
[TD]No
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]AA3
[/TD]
[TD][/TD]
[TD]Yes
[/TD]
[TD]Yes
[/TD]
[TD]Yes
[/TD]
[TD]No
[/TD]
[TD]No
[/TD]
[TD]No
[/TD]
[TD]No
[/TD]
[TD]No
[/TD]
[TD]Yes
[/TD]
[TD]Yes
[/TD]
[TD]Yes
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]AA4
[/TD]
[TD][/TD]
[TD]No
[/TD]
[TD]No
[/TD]
[TD]No
[/TD]
[TD]No
[/TD]
[TD]No
[/TD]
[TD]No
[/TD]
[TD]No
[/TD]
[TD]Yes
[/TD]
[TD]Yes
[/TD]
[TD]Yes
[/TD]
[TD]Yes
[/TD]
[TD]Yes
[/TD]
[/TR]
</tbody>[/TABLE]

In C5 and copy across and down

=IF(COUNTIFS(Usage!$B$2:$B$20,">="&C$3,Usage!$B$2:$B$20,"<="&C$4,Usage!$A$2:$A$20,$A5)>0,"Yes","No")

 

[reachsuresh87]

That's amazing Scott, you saved my life...
Thank you very much.

 

[Scott T]

glad to help

 

[arthurbr]

cross post without links reported