How to create a formula for Multi Criteria lookup with dates

< Back to Search results

 Category: [Excel]  Demo Available 

How to create a formula for Multi Criteria lookup with dates




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.


See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets


See also:

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


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


stars (0 Reviews)