lookup based on date range

  • Thread starter Thread starter CTEagle91
  • Start date Start date
C

CTEagle91

Hi Gurus! I've fried my brain trying to figure this out (maybe there wasn't
much to fry?) Anyway...

I want to lookup the correct hourly rate of an employee in a table. Each
employee will have a unique name, but may have gotten a raise a few times
over the years and will have multiple rows in the table - one row for each
rate they've had. Each row will have a Start and an End date where that pay
rate was effective.

Something like this:
A B C D
1 Name Start End Rate
2 Joe 01/01/08 12/31/09 $11
3 Mary 01/01/07 04/30/08 $11
4 Mary 05/01/08 12/31/08 $14
5 Mary 01/01/09 12/31/09 $19

I want to lookup the correct Rate for Mary based on the date she worked.
For example, if she put in 4 hours at work today (02/12/09), how do I lookup
her correct rate?
 
If your table is sorted so that the most recent pay rate is the last
chronological pay rate for the employee (as is demonstrated in your sample):

F2 = Mary

=LOOKUP(2,1/(A2:A5=F2),D2:D5)
 
Sorry for the dup question... I got an error on the first one and didn't
think it went through.
 
Hi,

If you don't know that the entries will be in ascending order by date then
you could use

In 2007:
=SUMIFS(D2:D5,A2:A5,F2,B2:B5,"<="&G2,C2:C5,">="&G2)

In 2003:
=SUMPRODUCT((A2:A5=F2)*(B2:B5<=G2)*(C2:C5>=G2)*D2:D5)

Where the employee name is in F2 and the date in G2.
 
Thank you, too, Shane... Biff came up with the same solution and it worked
great (I'm still on Excel 2003). I really appreciate the quick response and
great ideas!!!
 
Back
Top