Lookup employee salary on a specified date

[stevejd]

Hello all - I'm sure that I should know how to do this but can't for the life of me work out how! I have two tabs, one shows all employees (including their unique employee number) with a separate row showing each salary period eg

name	employee ref	salary	period start	period end
smith	12345678	20,000	01/04/2013	31/03/2014
smith	12345678	25,000	01/04/2014	31/03/2015
jones	23456789	27,500	17/03/2014	31/03/2015

<tbody>
</tbody>

the second tab shows a list of allowances paid to staff and the date that they were paid - for each allowance, I need to look up the employee's salary at the time that the allowance was paid, eg

name	employee ref	allowance type	date paid	salary
smith	12345678	overtime x1.5	17/08/2013	?
smith	12345678	overtime x1.5	27/09/2013	?
smith	12345678	additional hours	03/04/2014	?

<tbody>
</tbody>

Help!

 

[Andrew Poulsom]

A couple of possibilities (formulas in E7:F7 copied down):

Excel 2010
	A	B	C	D	E	F
1	name	employee ref	salary	period start	period end
2	smith	12345678	20,000	01/04/2013	31/03/2014
3	smith	12345678	25,000	01/04/2014	31/03/2015
4	jones	23456789	27,500	17/03/2014	31/03/2015
5
6	name	employee ref	allowance type	date paid	salary	salary
7	smith	12345678	overtime x1.5	17/08/2013	20000	20000
8	smith	12345678	overtime x1.5	27/09/2013	20000	20000
9	smith	12345678	additional hours	03/04/2014	25000	25000
Sheet9

Cell Formulas
Range	Formula
E7	=LOOKUP(2,1/((B$2:B$4=B7)*(D$2:D$4<=D7)*(E$2:E$4>=D7)),C$2:C$4)
F7	=INDEX(C$2:C$4,MATCH(1,INDEX((B$2:B$4=B7)*(D$2:D$4<=D7)*(E$2:E$4>=D7),),0))

 

[stevejd]

I can't speak for the lookup, but the Index formula worked like a dream (even if looking up 220,000 allowances against 27,000 employee records meant that it took 45 minutes to process!)

Many thanks for your help Andrew