Using SUMPRODUCT to look up a date in a range and return a value???

[SAMCRO2014]

Hi there. I am attempting to create a loop using SUMPRODUCT to find a date within a date range for an employee number and returning a value. This is my situation.

I have an overtime workbook that list the dates (Column B) and hours of overtime worked (Column E) by employee number (Column A). I need to have the macro look up the hourly rate (Column V) in a separate workbook (Atlantic). The way the information is exported for 'Atlantic' is each employee number (Column H) has a start date (Column I) and end date (Column J) for a specific hourly wage (Column V).

For example:

Employee # 999999 worked overtime on:
- April 4, 2018 for 10.50 hours
- April 28, 2018 for 5 hours
- October 25, 2018 for 4 hours

Employee # 999999 hourly rates were:
- April 1, 2018 - August 23, 2018 $15
- August 24, 2018 - October 19, 2018 $18
- October 20, 2018 - March 31, 2019 $20

Required:
- Bring back the hourly rate for each day worked of overtime.

I have myself so confused on how to even start this even after watching YouTube videos.

Any advice?

 

[AlanY]

can you work on this?

Book1
	A	B	C	D	E	F	G	H	I
1	Employee	Date	Hours	O/T Rate		Employee	Start	End	Rate
2	101	04 April 2018	10.5	15		101	01 April 2018	23 August 2018	15
3	101	28 April 2018	5	15		101	24 August 2018	19 October 2018	18
4	101	25 October 2018	4	20		101	20 October 2018	31 March 2019	20
Sheet1

Cell Formulas
Range	Formula
D2	=INDEX($I$2:$I$4,SUMPRODUCT(--(B2>=$G$2:$G$4)*--(B2<=$H$2:$H$4),ROW($A$2:$A$4)-ROW($A$1)))

 

[SAMCRO2014]

I guess my problem will be will be with the INDEX range which will be different for each employee number. Some employees will have only one hourly rate for the entire year while others can have up to six hourly rate changes in a year. Also, the information is in two different workbooks.

I just tried it and it did not return the value I needed.