The user is trying to do a multi layer lookup.
The data includes employee ID# with a range of their payscale (start and end), once they get a raise the start and end date of the payroll will update.
There are two criteria on the main data page, Employee ID# and date of check.
The user needs to search this criteria through a list that the user has containing employee ID, Start date of pay, End date of Pay, and the dollar amount.
The user needs need a multiple layer lookup to search by employee ID and get their pay based on the date of the check. I will attached example items.
This is the code:
Option Explicit Sub findwage() Dim i As Long, j As Long Dim lr As Long, lr2 As Long lr = Range("B" & Rows.Count).End(xlUp).Row lr2 = Range("H" & Rows.Count).End(xlUp).Row Application.ScreenUpdating = False For i = 5 To lr For j = 5 To lr2 If Range("C" & i) = Range("H" & j) Then If Range("B" & i) >= Range("I" & j) And Range("B" & i) <= Range("J" & j) Then Range("E" & i) = Range("K" & j) End If End If Next j Next i Application.ScreenUpdating = True MsgBox "Complete" End Sub
This is the formula:
Obtained from the OzGrid Help Forum.
Solution provided by Alan Sidman and Fluff13.
|How to calculate duration difference between two dates|
|How to convert US to UK dates|
|How to use SUMPRODUCT with dates|
|How to sum up values in a date range|
|How to compare 2 date ranges when name matches|
|How to average time between dates|