Formula for historic pay rate

C

Charles

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!
 
B

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top