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

#### ahmedanis7

##### New Member
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.

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### StefanVL

##### Board Regular
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

##### New Member
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

##### Board Regular
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??

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

Replies
3
Views
762
Replies
0
Views
564
Replies
2
Views
2K
Replies
0
Views
267
Replies
0
Views
247

1,196,027
Messages
6,012,950
Members
441,740
Latest member
abaz21

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back