Help! Trying to pull historical employee data based on a date range.

[livesmiley]

I have one file (Pay Statement) with Employee numbers (column B) and pay period dates (column E). In column M I am trying to pull in data from another file (History). The History file has the employee number (column A), the effective begin date (column C), effective end date (column D) and the chosen amount (column J). I am needing to pull the historical amount that was valid at the pay period end date for that particular employee. Any help you can provide would be greatly appreciated! Thanks!

 

[Toadstool]

Hi livesmiley,

You don't say how many rows of data you have so I'm checking every row on the sheet. The formula will run faster if you limit the row range.

Here's the historical data sheet. No formulae here, just the data. I'm assuming they all have an effective end date and no date ranges overlap for an employee:

	A	B	C	D	E	J
Name
Alf Carruthers
Bert Jones
Bert Jones
Charlie White
Charlie White
Hannah Ross
Sarah Pollard
Stella Mitchell

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Employee Id[/TD]

[TD="align: center"]Effective Begin Date[/TD]
[TD="align: center"]Effective End Date[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Historical Amount[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]A154[/TD]

[TD="align: center"]02-Feb-19[/TD]
[TD="align: center"]24-May-19[/TD]
[TD="align: right"][/TD]
[TD="align: center"]$18,888[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]B189[/TD]

[TD="align: center"]21-Feb-19[/TD]
[TD="align: center"]12-Jun-19[/TD]
[TD="align: right"][/TD]
[TD="align: center"]$19,955[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]B189[/TD]

[TD="align: center"]13-Jun-19[/TD]
[TD="align: center"]02-Oct-19[/TD]
[TD="align: right"][/TD]
[TD="align: center"]$21,222[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]C132[/TD]

[TD="align: center"]31-Mar-19[/TD]
[TD="align: center"]20-Jul-19[/TD]
[TD="align: right"][/TD]
[TD="align: center"]$12,487[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]C132[/TD]

[TD="align: center"]21-Jul-19[/TD]
[TD="align: center"]05-Oct-19[/TD]
[TD="align: right"][/TD]
[TD="align: center"]$13,985[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]H165[/TD]

[TD="align: center"]22-Apr-19[/TD]
[TD="align: center"]27-Nov-19[/TD]
[TD="align: right"][/TD]
[TD="align: center"]$15,664[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]S396[/TD]

[TD="align: center"]12-Apr-19[/TD]
[TD="align: center"]16-Dec-19[/TD]
[TD="align: right"][/TD]
[TD="align: center"]$17,543[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]S429[/TD]

[TD="align: center"]12-Apr-19[/TD]
[TD="align: center"]04-Jan-20[/TD]
[TD="align: right"][/TD]
[TD="align: center"]$19,649[/TD]

</tbody>

Historical​

Here's the Pay Statement with the formula:

	A	B	C	D	E	F	M
Name
Alf Carruthers
Bert Jones
Charlie White
Hannah Ross
Sarah Pollard
Stella Mitchell

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]Employee Id[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Pay Period Date[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Historical Amount[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]A154[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]03-Mar-19[/TD]
[TD="align: right"][/TD]
[TD="align: center"]$18,888[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]B189[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]03-Aug-19[/TD]
[TD="align: right"][/TD]
[TD="align: center"]$21,222[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]C132[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]06-Apr-19[/TD]
[TD="align: right"][/TD]
[TD="align: center"]$12,487[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]H165[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]23-Apr-19[/TD]
[TD="align: right"][/TD]
[TD="align: center"]$15,664[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]S396[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]20-Aug-19[/TD]
[TD="align: right"][/TD]
[TD="align: center"]$17,543[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]S429[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]06-Sep-19[/TD]
[TD="align: right"][/TD]
[TD="align: center"]$19,649[/TD]

</tbody>

Pay Statement​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]M2
copied down
[/TH]
[TD="align: left"]=INDEX(Historical!J:J,AGGREGATE(14,6,ROW(Historical!A:A)/((B2=Historical!A:A)*(E2>=Historical!C:C)*(E2<=Historical!D:D)),1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]