Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup based on date range
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup based on date 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup based on date range
Sorry for the dup question... I got an error on the first one and didn't
think it went through. "CTEagle91" wrote: 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup based on date range
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. -- If this helps, please click the Yes button Cheers, Shane Devenshire "CTEagle91" wrote: 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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup based on date range
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!!! "Shane Devenshire" wrote: 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. -- If this helps, please click the Yes button Cheers, Shane Devenshire "CTEagle91" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup based on a date being between a range | Excel Worksheet Functions | |||
Lookup Hours from table based on date range... | Excel Discussion (Misc queries) | |||
Add worksheet Lookup based on date | Excel Worksheet Functions | |||
Lookup based on range of dates | Excel Worksheet Functions | |||
Lookup with search range start based on position of last blank lin | Excel Worksheet Functions |