Formula Date Lookup with a Vlookup

[brandy5122000]

I have an issues with trying to pull the correct data. My scenario is I have an employee number with a worked date next to it (tab 1). I also have a tab (tab 2) that shows by employee number when an employee was active. The issues I am having is on this tab his active status could be multiple lines (due to being active on and off). I am looking for a way to retrieve the status (from tab 2) if the dates fall in that range and insert them on tab 2 (like a vlookup). Any help would be appreciated.

Tab 1

EMP_NUM	DATE
113	15-Apr-13
113	16-Apr-13
113	4-Oct-13
113	5-Oct-13
113	6-Oct-13
113	11-Dec-13
113	12-Dec-13
113	13-Dec-13
120	21-Jan-13
120	22-Jan-13
140	3-Sep-13
140	4-Sep-13
140	5-Sep-13
140	6-Sep-13
140	23-Dec-13
208	7-Jan-13
208	8-Jan-13
208	4-May-13
208	5-May-13
208	12-Dec-13
208	19-Dec-13

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

Tab 2

EMP Num Status Start Date End Date 113 Overseas 09/28/2013 11/01/2013 208 Overseas 04/06/2013 09/06/2013 588 Overseas 11/10/2012 04/26/2013 120 US 1/1/2013 4/1/2013 120 Overseas 9/15/2013 12/31/2013 140 Home 2/5/2013 4/6/2013 140 US 7/25/2013 10/30/2013 140 Overseas 11/15/2013 2/7/2014 <colgroup><col span="2"><col span="2"></colgroup><tbody> </tbody>

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

 

[Asala42]

Not the prettiest of formulas, but this seems to work:

=IFERROR(INDEX(Sheet2!$B$1:$B$1000,IF(SUMPRODUCT(--(Sheet2!$A$1:$A$1000=A2),--(Sheet2!$C$1:$C$1000<=B2),--(Sheet2!$D$1:$D$1000>B2),ROW(Sheet2!$A$1:$A$1000))=0,"-",SUMPRODUCT(--(Sheet2!$A$1:$A$1000=A2),--(Sheet2!$C$1:$C$1000<=B2),--(Sheet2!$D$1:$D$1000>B2),ROW(Sheet2!$A$1:$A$1000))),1),"-")

It assumes there's no overlap in dates for the same EMP_Num.