Lookup based on a date being between a range
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)
--
Biff
Microsoft Excel MVP
"CTEagle91" wrote in message
...
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?
|