Aug 31 2021 06:45 PM
Aug 31 2021 06:45 PM
I need to find an hourly rate for an employee the range falls within the date that the overtime was worked.
Here is a sample of the data set
Can somebody help me with a formulae to lookup with both criteria?
Aug 31 2021 07:41 PM
Both criteria? The only criterion that I see described is the date. So let me suggest:
Sep 01 2021 05:57 AM
Thank you for posting the spreadsheet. But at first it was more confusing than your original post. The workbook has a front sheet (Sheet1) that contains a few formulas (VLOOKUP and INDEX/MATCH) but they don't refer to the Sheet2, which is where the data you originally posted in image form. Nor is it readily apparent what criteria are being used.
So could you please spell out more clearly what it is you want done with the data on Sheet2 (or Sheet1).
That said, since you specified two criteria at the start, may I point you in the direction of a possible solution. If you have the newest version of Excel, there are some new "Dynamic Array functions," FILTER and UNIQUE among them, that might be the solution you're looking for. Here's a YouTube video that explains them....from the formulas you've written, it would appear that you do have a good grasp of some of the basics of data retrieval from an Excel database.....these new functions take that to a whole new level. https://www.youtube.com/watch?v=9I9DtFOVPIg
But you will need the newest version of Excel for these Dynamic Array functions to work
Sep 01 2021 12:26 PM - edited Sep 02 2021 03:35 PM
I am removing my suggested solution because the suggested solution was only for the presented dataset and not for the 4000 lines of variant [Start Date] and [End Date] for 130 other employees. to get an accurate solution, the [Start Date] column and the [End Date] column must be in a separate table with unique start and end date ranges and must include all date ranges to compare against the 4000 OT dates. Maybe try @Sergei Baklan solution.
As a variant, here is a sample file for what is being described above.
Where Periods table must have unique ranges
That way, if an employee has OT in the same period on a different day
or a different employee has OT on the same day as 129 other employees the comparison is not looking at multiple instances of the same period range that will skew the result the solution will still accurately detect it since the setup makes it that much granular and not lumpy
Sep 02 2021 01:57 AM
Sep 02 2021 04:19 AM
Sep 02 2021 12:21 PMSolution
Based on initial screenshot that could be like
=LOOKUP(2, 1/ ( $G2:$G9 = A2 )/ (C2>=$H$2:$H$9)/ (C2<=$I$2:$I$9), $J$2:$J$9)
For entire range, using dynamic one
=LOOKUP(2, 1/ ( $G$2:INDEX($G:$G, COUNTA($G:$G)) = $A2 ) / ( $C2 >= $H$2:INDEX($H:$H, COUNTA($H:$H)) )/ ( $C2 <= $I$2:INDEX($I:$I, COUNTA($I:$I)) ), $J$2:INDEX($J:$J, COUNTA($J:$J)))