Reference Lookup need for a Name and Date set

[TJ flyer]

I am trying to look at a user log data set and assign a team based on the user name and the date. I have a seperate table with user names and the dates they were on the different teams. VLOOKUP will find the first entry for the user but I can't figure out how to have it also look at the date range. Any Suggestions or ideas?

---

User Time Log, trying to write a formula that will use the Team Assignment table and fill in the correct value. The expected result is in the last column for reference.

Date	Name	Hours	Team(Formula)	Expected Team
2/1/2016	Amy	5.0		Team 1
3/3/2016	Amy	4.5		Team 1
7/7/2016	Amy	6.0		Team 2
8/2/2016	Amy	5.0		Team 2
2/1/2016	Bob	4.5		Team a
3/2/2016	Bob	6.0		Team b
7/3/2016	Bob	7.0		Team b
2/9/2016	Jim	6.0		Team c
8/5/2016	Jim	2.0		Leader

<tbody>
</tbody>

Team Assignment Table:

Name	Start Date	End Date	Team
Amy	1/1/2016	4/1/2016	Team 1
Amy	4/2/2016	12/9/2016	Team 2
Bob	1/1/2016	3/1/2016	Team a
Bob	3/2/2016		Team b
Jim	1/1/2016	7/1/2016	Team c
Jim	7/2/2016		Leader

<tbody>
</tbody>

 

[63falcondude]

With your first range in A1:E10 and your second range in G1:J7 (headers in row 1), try this in D2:

=LOOKUP(2,1/((G$2:G$7=B2)*(H$2:H$7<=A2)*((I$2:I$7>=A2)+(I$2:I$7=""))),J$2:J$7)

 

[TJ flyer]

Perfect! Works like a charm. Now I wish I hadn't banged my head against that wall all day yesterday. I need to turn to the wise community sooner.

 

[63falcondude]

Great! Happy to help.