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
[TABLE="width: 166"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]EMP_NUM[/TD]
[TD="align: center"] DATE[/TD]
[/TR]
[TR]
[TD="align: right"]113[/TD]
[TD="align: right"]15-Apr-13[/TD]
[/TR]
[TR]
[TD="align: right"]113[/TD]
[TD="align: right"]16-Apr-13[/TD]
[/TR]
[TR]
[TD="align: right"]113[/TD]
[TD="align: right"]4-Oct-13[/TD]
[/TR]
[TR]
[TD="align: right"]113[/TD]
[TD="align: right"]5-Oct-13[/TD]
[/TR]
[TR]
[TD="align: right"]113[/TD]
[TD="align: right"]6-Oct-13[/TD]
[/TR]
[TR]
[TD="align: right"]113[/TD]
[TD="align: right"]11-Dec-13[/TD]
[/TR]
[TR]
[TD="align: right"]113[/TD]
[TD="align: right"]12-Dec-13[/TD]
[/TR]
[TR]
[TD="align: right"]113[/TD]
[TD="align: right"]13-Dec-13[/TD]
[/TR]
[TR]
[TD="align: right"]120[/TD]
[TD="align: right"]21-Jan-13[/TD]
[/TR]
[TR]
[TD="align: right"]120[/TD]
[TD="align: right"]22-Jan-13[/TD]
[/TR]
[TR]
[TD="align: right"]140[/TD]
[TD="align: right"]3-Sep-13[/TD]
[/TR]
[TR]
[TD="align: right"]140[/TD]
[TD="align: right"]4-Sep-13[/TD]
[/TR]
[TR]
[TD="align: right"]140[/TD]
[TD="align: right"]5-Sep-13[/TD]
[/TR]
[TR]
[TD="align: right"]140[/TD]
[TD="align: right"]6-Sep-13[/TD]
[/TR]
[TR]
[TD="align: right"]140[/TD]
[TD="align: right"]23-Dec-13[/TD]
[/TR]
[TR]
[TD="align: right"]208[/TD]
[TD="align: right"]7-Jan-13[/TD]
[/TR]
[TR]
[TD="align: right"]208[/TD]
[TD="align: right"]8-Jan-13[/TD]
[/TR]
[TR]
[TD="align: right"]208[/TD]
[TD="align: right"]4-May-13[/TD]
[/TR]
[TR]
[TD="align: right"]208[/TD]
[TD="align: right"]5-May-13[/TD]
[/TR]
[TR]
[TD="align: right"]208[/TD]
[TD="align: right"]12-Dec-13[/TD]
[/TR]
[TR]
[TD="align: right"]208[/TD]
[TD="align: right"]19-Dec-13[/TD]
[/TR]
</tbody>[/TABLE]


Tab 2

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

 

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