# OzGrid

### 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```

OR

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.