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
1	Employee Id	Name	Effective Begin Date	Effective End Date		Historical Amount
2	A154	Alf Carruthers	02-Feb-19	24-May-19		$18,888
3	B189	Bert Jones	21-Feb-19	12-Jun-19		$19,955
4	B189	Bert Jones	13-Jun-19	02-Oct-19		$21,222
5	C132	Charlie White	31-Mar-19	20-Jul-19		$12,487
6	C132	Charlie White	21-Jul-19	05-Oct-19		$13,985
7	H165	Hannah Ross	22-Apr-19	27-Nov-19		$15,664
8	S396	Sarah Pollard	12-Apr-19	16-Dec-19		$17,543
9	S429	Stella Mitchell	12-Apr-19	04-Jan-20		$19,649

<tbody>
</tbody>

Historical​

Here's the Pay Statement with the formula:

	A	B	C	D	E	F	M
1	Name	Employee Id			Pay Period Date		Historical Amount
2	Alf Carruthers	A154			03-Mar-19		$18,888
3	Bert Jones	B189			03-Aug-19		$21,222
4	Charlie White	C132			06-Apr-19		$12,487
5	Hannah Ross	H165			23-Apr-19		$15,664
6	Sarah Pollard	S396			20-Aug-19		$17,543
7	Stella Mitchell	S429			06-Sep-19		$19,649

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Pay Statement​

Worksheet Formulas Cell Formula M2 copied down =INDEX(Historical!J:J,AGGREGATE(14,6,ROW(Historical!A:A)/((B2=Historical!A:A)*(E2>=Historical!C:C)*(E2<=Historical!D:D)),1)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>