Find which date range a date is within and return another column

[APJo]

Hello,

Say I have two tabs in a spreadsheet. One has a list of boxes, the dates on which they were opened and the team member who opened them. The other has a list of the team members who open the boxes and when they were in which team (For example, Jenny moves from the Chickens team to the Ducks on October 25).

I need to be able to find out which team opened which box. Any ideas??

I have included example data in the attachment below.

Thank you in advance.

Jo.Excel Help.xlsx

[Tony Valko]

Try this...

Data Range

	B	C	D	E
3	Box	Team Member	Opening Date	Team
4	Red Box	Teddy	5/31/2014	Roosters
5	Blue Box	Jenny	6/6/2014	Chickens
6	Green Box	Teddy	10/24/2014	Roosters
7	Yellow Box	Felicity	7/15/2014	Roosters
8	Pink Box	Felicity	2/3/2014	Roosters
9	Orange Box	Julia	8/3/2014	Ducks

This array formula** entered in E4 and copied down:

=INDEX(Teams!D$2:D$7,MATCH(1,(Teams!A$2:A$7=C4)*(D4>=Teams!B$2:B$7)*(D4<=Teams!C$2:C$7),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.