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

#### SAMCRO2014

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

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
can you work on this?

Book1
ABCDEFGHI
1EmployeeDateHoursO/T RateEmployeeStartEndRate
210104 April 201810.51510101 April 201823 August 201815
310128 April 201851510124 August 201819 October 201818
410125 October 201842010120 October 201831 March 201920
Sheet1
Cell Formulas
RangeFormula
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)))

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.

Last edited:

Replies
5
Views
344
Replies
0
Views
120
Replies
6
Views
487
Replies
0
Views
319
Replies
2
Views
840

1,203,604
Messages
6,056,225
Members
444,852
Latest member
MJaspering

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