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

Week

201710

201711

201712

201713

201714

201715

201716

201717

201718

201719

201720

201721

StartDate

02/13/17

02/20/17

02/27/17

03/06/17

03/13/17

03/20/17

03/27/17

04/03/17

04/10/17

04/17/17

04/24/17

05/01/17

EndDate

03/12/17

03/19/17

03/26/17

04/02/17

04/09/17

04/16/17

04/23/17

04/30/17

05/07/17

05/14/17

05/21/17

05/28/17

Name

AA1

Check if name in A5 present in 'Usage' sheet with dates between StartDate and EndDate

No

No

No

No

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

AA2

Check if name in A6 present in 'Usage' sheet with dates between StartDate and EndDate

Yes

Yes

Yes

Yes

No

No

No

No

No

No

No

No

AA3

Check if name in A7 present in 'Usage' sheet with dates between StartDate and EndDate

Yes

Yes

Yes

No

No

No

No

No

Yes

Yes

Yes

Yes

AA4

Check if name in A8 present in 'Usage' sheet with dates between StartDate and EndDate

No

No

No

No

No

No

No

Yes

Yes

Yes

Yes

Yes

<colgroup><col><col><col><col span="11"></colgroup><tbody>
</tbody>

Name	Day
AA2	2/13/2017
AA2	2/13/2017
AA2	2/23/2017
AA2	2/24/2017
AA2	2/24/2017
AA2	2/24/2017
AA3	3/1/2017
AA3	3/1/2017
AA2	3/7/2017
AA2	3/10/2017
AA1	4/6/2017
AA1	4/6/2017
AA4	4/25/2017
AA4	5/3/2017
AA3	5/7/2017
AA3	5/7/2017
AA3	5/7/2017
AA3	5/7/2017
AA1	5/22/2017

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[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.

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1

2

201710

201711

201712

201713

201714

201715

201716

201717

201718

201719

201720

201721

3

2/13/2017

2/20/2017

2/27/2017

3/6/2017

3/13/2017

3/20/2017

3/27/2017

4/3/2017

4/10/2017

4/17/2017

4/24/2017

5/1/2017

4

3/12/2017

3/19/2017

3/26/2017

4/2/2017

4/9/2017

4/16/2017

4/23/2017

4/30/2017

5/7/2017

5/14/2017

5/21/2017

5/28/2017

5

AA1

No

No

No

No

Yes

Yes

Yes

Yes

No

No

No

Yes

6

AA2

Yes

Yes

Yes

Yes

No

No

No

No

No

No

No

No

7

AA3

Yes

Yes

Yes

No

No

No

No

No

Yes

Yes

Yes

Yes

8

AA4

No

No

No

No

No

No

No

Yes

Yes

Yes

Yes

Yes

<tbody>
</tbody>

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