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

SAMCRO2014

Board Regular
Joined
Sep 3, 2015
Messages
160
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?
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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)))
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,219
Members
452,620
Latest member
dsubash

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top