Vlookup and if date is between date range

[bn10ai]

In Sheet 1 I have the following: A the date of a game, and C the team, I'm missing the manager at that time which is in sheet 2.
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A (DATE)[/TD]
[TD]B (MANAGER)[/TD]
[TD]C (TEAM[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]01/05/1994[/TD]
[TD][/TD]
[TD]Man Utd[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]01/05/1994[/TD]
[TD][/TD]
[TD]Arsenal[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]01/05/1994[/TD]
[TD][/TD]
[TD]Everton[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]01/05/1994[/TD]
[TD][/TD]
[TD]Chelsea[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]13/10/1994[/TD]
[TD][/TD]
[TD]Man Utd[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]02/05/1995[/TD]
[TD][/TD]
[TD]Arsenal[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]02/05/1995[/TD]
[TD][/TD]
[TD]Liverpool[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]10/10/1996[/TD]
[TD][/TD]
[TD]Liverpool[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]14/03/1997[/TD]
[TD][/TD]
[TD]Everton[/TD]
[/TR]
</tbody>[/TABLE]
















In Sheet 2 I have the managers start date and end date for every club

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A (CLUB)[/TD]
[TD]B (DATE FROM)[/TD]
[TD]C (DATE END)[/TD]
[TD]D (MANAGER)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Arsenal[/TD]
[TD]14/05/1986[/TD]
[TD]21/02/1995[/TD]
[TD]George Graham[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Arsenal[/TD]
[TD]22/02/1995[/TD]
[TD]08/06/1995[/TD]
[TD]Stewart Houston[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Arsenal[/TD]
[TD]08/06/1995[/TD]
[TD]12/08/1996[/TD]
[TD]Bruce Rioch[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Arsenal[/TD]
[TD]12/08/1996[/TD]
[TD]13/09/1996[/TD]
[TD]Stewart Houston[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Man Utd[/TD]
[TD]06/11/1986[/TD]
[TD]01/07/2013[/TD]
[TD]Alex Ferguson[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Man Utd[/TD]
[TD]01/07/2013[/TD]
[TD]22/04/2014[/TD]
[TD]David Moyes[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Man Utd[/TD]
[TD]22/04/2014[/TD]
[TD]14/07/2014[/TD]
[TD]Ryan Giggs[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Everton[/TD]
[TD]05/11/1990[/TD]
[TD]04/12/1993[/TD]
[TD]Howard Kendall[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Everton[/TD]
[TD]04/12/1993[/TD]
[TD]07/01/1994[/TD]
[TD]Jimmy Gabriel[/TD]
[/TR]
</tbody>[/TABLE]
















This data carries on right through to present day, I will never have a date in sheet 1 that isn't sufficed in sheet 2, and all clubs are present..

So in sheet 1 i need a formula that could find the manager of the club for the date of the game.

Somehow combining looking up the manager of that team, and the date falling within the date range in sheet 2.. I can add/change columns and column orders etc if I need to. I've been scratching my head around forums all day to find a solution, looking at v look ups with multiple conditions etc. but cant seem to find one that works! this is only a tiny bit of the data, hence why I'm not being lazy and just do it manually! When i think logically as to how this function could work, i would ask it to find the cells where the date falls within the date range, then look up the club I'm trying to find, to return the manager

Thanks in advance for any recommendations, and apologies if I'm being stupid, its late.

 

[Weazel]

maybe something like...

Excel 2010[TABLE="class: grid, width: 500"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]A (CLUB)[/TD]
[TD]B (DATE FROM)[/TD]
[TD]C (DATE END)[/TD]
[TD]D (MANAGER)[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Arsenal[/TD]
[TD="align: right"]14/05/1986[/TD]
[TD="align: right"]21/02/1995[/TD]
[TD]George Graham[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Arsenal[/TD]
[TD="align: right"]22/02/1995[/TD]
[TD="align: right"]08/06/1995[/TD]
[TD]Stewart Houston[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Arsenal[/TD]
[TD="align: right"]08/06/1995[/TD]
[TD="align: right"]12/08/1996[/TD]
[TD]Bruce Rioch[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Arsenal[/TD]
[TD="align: right"]12/08/1996[/TD]
[TD="align: right"]13/09/1996[/TD]
[TD]Stewart Houston[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]Man Utd[/TD]
[TD="align: right"]06/11/1986[/TD]
[TD="align: right"]01/07/2013[/TD]
[TD]Alex Ferguson[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]Man Utd[/TD]
[TD="align: right"]01/07/2013[/TD]
[TD="align: right"]22/04/2014[/TD]
[TD]David Moyes[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]Man Utd[/TD]
[TD="align: right"]22/04/2014[/TD]
[TD="align: right"]14/07/2014[/TD]
[TD]Ryan Giggs[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]Everton[/TD]
[TD="align: right"]05/11/1990[/TD]
[TD="align: right"]04/12/1993[/TD]
[TD]Howard Kendall[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]Everton[/TD]
[TD="align: right"]04/12/1993[/TD]
[TD="align: right"]07/01/1994[/TD]
[TD]Jimmy Gabriel[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="bgcolor: #FAFAFA"]A (DATE)[/TD]
[TD="bgcolor: #FAFAFA"]B (MANAGER)[/TD]
[TD="bgcolor: #FAFAFA"]C (TEAM[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="bgcolor: #FAFAFA, align: right"]01/05/1994[/TD]
[TD="bgcolor: #FAFAFA"]Alex Ferguson[/TD]
[TD="bgcolor: #FAFAFA"]Man Utd[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="bgcolor: #FAFAFA, align: right"]01/05/1994[/TD]
[TD="bgcolor: #FAFAFA"]George Graham[/TD]
[TD="bgcolor: #FAFAFA"]Arsenal[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="bgcolor: #FAFAFA, align: right"]01/05/1994[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"]Everton[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="bgcolor: #FAFAFA, align: right"]01/05/1994[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"]Chelsea[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="bgcolor: #FAFAFA, align: right"]13/10/1994[/TD]
[TD="bgcolor: #FAFAFA"]Alex Ferguson[/TD]
[TD="bgcolor: #FAFAFA"]Man Utd[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="bgcolor: #FAFAFA, align: right"]02/05/1995[/TD]
[TD="bgcolor: #FAFAFA"]Stewart Houston[/TD]
[TD="bgcolor: #FAFAFA"]Arsenal[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD="bgcolor: #FAFAFA, align: right"]02/05/1995[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"]Liverpool[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10/10/1996[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"]Liverpool[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD="bgcolor: #FAFAFA, align: right"]14/03/1997[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"]Everton[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B13[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet2!$D$2:$D$10,MATCH(C13,IF((A13>=Sheet2!$B$2:$B$10)*(A13<=Sheet2!$C$2:$C$10),Sheet2!$A$2:$A$10),0)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[bn10ai]

Works perfectly thank you very much!