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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Name[/TD]
[TD]Hours[/TD]
[TD]Team(Formula)[/TD]
[TD]Expected Team[/TD]
[/TR]
[TR]
[TD]2/1/2016[/TD]
[TD]Amy[/TD]
[TD]5.0[/TD]
[TD][/TD]
[TD]Team 1[/TD]
[/TR]
[TR]
[TD]3/3/2016[/TD]
[TD]Amy[/TD]
[TD]4.5[/TD]
[TD][/TD]
[TD]Team 1[/TD]
[/TR]
[TR]
[TD]7/7/2016[/TD]
[TD]Amy[/TD]
[TD]6.0[/TD]
[TD][/TD]
[TD]Team 2[/TD]
[/TR]
[TR]
[TD]8/2/2016[/TD]
[TD]Amy[/TD]
[TD]5.0[/TD]
[TD][/TD]
[TD]Team 2[/TD]
[/TR]
[TR]
[TD]2/1/2016[/TD]
[TD]Bob[/TD]
[TD]4.5[/TD]
[TD][/TD]
[TD]Team a[/TD]
[/TR]
[TR]
[TD]3/2/2016[/TD]
[TD]Bob[/TD]
[TD]6.0[/TD]
[TD][/TD]
[TD]Team b[/TD]
[/TR]
[TR]
[TD]7/3/2016[/TD]
[TD]Bob[/TD]
[TD]7.0[/TD]
[TD][/TD]
[TD]Team b
[/TD]
[/TR]
[TR]
[TD]2/9/2016[/TD]
[TD]Jim[/TD]
[TD]6.0[/TD]
[TD][/TD]
[TD]Team c[/TD]
[/TR]
[TR]
[TD]8/5/2016[/TD]
[TD]Jim[/TD]
[TD]2.0[/TD]
[TD][/TD]
[TD]Leader[/TD]
[/TR]
</tbody>[/TABLE]

Team Assignment Table:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Team[/TD]
[/TR]
[TR]
[TD]Amy[/TD]
[TD]1/1/2016[/TD]
[TD]4/1/2016[/TD]
[TD]Team 1[/TD]
[/TR]
[TR]
[TD]Amy[/TD]
[TD]4/2/2016[/TD]
[TD]12/9/2016[/TD]
[TD]Team 2[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]1/1/2016[/TD]
[TD]3/1/2016[/TD]
[TD]Team a[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]3/2/2016[/TD]
[TD][/TD]
[TD]Team b[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]1/1/2016[/TD]
[TD]7/1/2016[/TD]
[TD]Team c[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]7/2/2016[/TD]
[TD][/TD]
[TD]Leader[/TD]
[/TR]
</tbody>[/TABLE]

 

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