Formula for historic pay rate

[Guest]

I have three columns of data Name, Date and Salary and a list of historic pay
days; I need a formula to look down these three columns, find the person in
Name and within their list of dates find the correct pay.

This is an example:
Name Date Salary
Brown 01/Jan/00 1000
Brown 01/Mar/01 1500
Brown 05/Jun/03 2000
Black 01/May/05 2500
Black 01/Sep/06 3000

If the pay date is 01/Oct/02 and the employee is Brown, I need a formula to
find Brown and then establish his pay rate which would be 1500.

I hope this is clear!

 

[Bob Phillips]

=INDEX(C2:C20,MAX(IF((A2:A20="Brown")*(B2:B20<=--"2002-10-01"),ROW(C2:C20)-M
IN(ROW(C2:C20))+1)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)