Lookup value for a date in a date range and return value

[ahmedanis7]

Dear Experts,

This is my first post and will be obliged for the help.

I have data in two sheets.

The first sheet "Day" contains following columns - Emp Number, Date.

The second sheet "Absence" has following columns - Emp Number , Start Date, End Date, Leave type

I need a formula or macro which will check if the Date as per the sheet "Day" falls between the start date and end date in sheet "Absence"and if yes, the type of leave is updated in Sheet "Day" .

Note- there could be multiple entries for same employee in the absence sheet.

Sheet "Day"

EMP NO DATE RESULT REQUIRED
123 15/09/2014 SICK LEAVE
123 17/09/2014 --
333 05/09/2014 --
333 10/09/2014 Annual Leave
333 12/09/2014 Annual Leave
333 16/09/2014 ---
333 18/09/2014 Sick Leave
333 19/09/2014 Sick Leave


Sheet "Absence"

Emp No Start Dtae End Date Leave
123 15/09/2014 15/09/2014 Sick Leave
333 10/09/2014 14/09/2014 Annual Leave
333 18/09/2014 20/09/2014 Sick Leave

Thanks.

 

[ahmedanis7]

Please help me

 

[StefanVL]

In C2 on sheet "day":

=IFERROR(INDEX(Absence!$D$2:$D$4;MATCH(1;IF(Absence!$A$2:$A$4=A2;IF(B2>=Absence!$B$2:$B$4;IF(B2<=Absence!$C$2:$C$4;1)));0));"- -")

Enter with CTRL + SHIFT + ENTER

 

[ahmedanis7]

Thanks. It worked. </SPAN>

</SPAN>.

There is so much to learn !!!

I had to replace the semi-colons with commas to make the formaula work. Is it got to do with the Excel Version I am using??</SPAN>

 

[StefanVL]

Thanks. It worked.

.

There is so much to learn !!!

I had to replace the semi-colons with commas to make the formaula work. Is it got to do with the Excel Version I am using??

Glad it worked!

the issue with semi-colons and commas is caused by the regional settings on your computer.