How to create a formula for Multi Criteria lookup with dates

Category: [Excel] Demo Available

How to create a formula for Multi Criteria lookup with dates

Requirement:

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.

Solution:

This is the code:

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:

=SUMPRODUCT((H$5:H$18=C5)*(I$5:I$18<B5)*(J$5:J$18>B5),(K$5:K$18))

Obtained from the OzGrid Help Forum.

Solution provided by Alan Sidman and Fluff13.

Gallery

stars (0 Reviews)

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
OzGrid is in no way associated with Microsoft.

Website designed by Astrava Solutions Ltd.