| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup based on a date being between a 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 a date being between a range
just in the mix to see the replies I have basically exc. same question posted
today "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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup based on a date being between a range
Hi Biff - Thanks for such a quick reply! The table will always be sorted
this way, but I'm not always looking for the most recent pay rate. For example, I want to put "Mary" in F2, and "08/01/08" in G2, and put some formula in H2 that will lookup the appropriate rate based on the name in F2 and the date in G2. "T. Valko" wrote: 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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup based on a date being between a range
little confused what this table array was in the SHEET2
mary was in column a1 and b2 would be the pay rate or dollar amount in sheet1. I have the ext. same quesiton up an I have gotten everything from an index, match, large and sum product formula I think if I saw this formula in the stucture I mentioned I could better understand please help "T. Valko" wrote: 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? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup based on a date being between a range
Dlotz - I'm really sorry, but I'm not understanding what you want
clarified... or by whom (me or Biff). Would you mind trying your question again a little more clearly? " wrote: little confused what this table array was in the SHEET2 mary was in column a1 and b2 would be the pay rate or dollar amount in sheet1. I have the ext. same quesiton up an I have gotten everything from an index, match, large and sum product formula I think if I saw this formula in the stucture I mentioned I could better understand please help "T. Valko" wrote: 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? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup based on a date being between a range
OK, try this:
F2 = Mary G2 = 8/1/2008 =SUMPRODUCT(--(A2:A5=F2),--(B2:B5<=G2),--(C2:C5=G2),D2:D5) -- Biff Microsoft Excel MVP "CTEagle91" wrote in message ... Hi Biff - Thanks for such a quick reply! The table will always be sorted this way, but I'm not always looking for the most recent pay rate. For example, I want to put "Mary" in F2, and "08/01/08" in G2, and put some formula in H2 that will lookup the appropriate rate based on the name in F2 and the date in G2. "T. Valko" wrote: 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? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup based on a date being between a range
we have the same question I m just trying to jump in and figure the same
thing out check out my post on "date range table array fromula" I think it might answer you question as well "CTEagle91" wrote: Dlotz - I'm really sorry, but I'm not understanding what you want clarified... or by whom (me or Biff). Would you mind trying your question again a little more clearly? " wrote: little confused what this table array was in the SHEET2 mary was in column a1 and b2 would be the pay rate or dollar amount in sheet1. I have the ext. same quesiton up an I have gotten everything from an index, match, large and sum product formula I think if I saw this formula in the stucture I mentioned I could better understand please help "T. Valko" wrote: 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? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup based on a date being between a range
I just looked at your post. The suggestions from Max should do the job.
-- Biff Microsoft Excel MVP " wrote in message ... we have the same question I m just trying to jump in and figure the same thing out check out my post on "date range table array fromula" I think it might answer you question as well "CTEagle91" wrote: Dlotz - I'm really sorry, but I'm not understanding what you want clarified... or by whom (me or Biff). Would you mind trying your question again a little more clearly? " wrote: little confused what this table array was in the SHEET2 mary was in column a1 and b2 would be the pay rate or dollar amount in sheet1. I have the ext. same quesiton up an I have gotten everything from an index, match, large and sum product formula I think if I saw this formula in the stucture I mentioned I could better understand please help "T. Valko" wrote: 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? |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup based on a date being between a range
Biff - I keep getting errors when I click the "Post" button so I'm not sure
my last reply went through (it hasn't shown up yet)... so, once again, in a nutshell... THANK YOU VERY MUCH! "T. Valko" wrote: OK, try this: F2 = Mary G2 = 8/1/2008 =SUMPRODUCT(--(A2:A5=F2),--(B2:B5<=G2),--(C2:C5=G2),D2:D5) -- Biff Microsoft Excel MVP "CTEagle91" wrote in message ... Hi Biff - Thanks for such a quick reply! The table will always be sorted this way, but I'm not always looking for the most recent pay rate. For example, I want to put "Mary" in F2, and "08/01/08" in G2, and put some formula in H2 that will lookup the appropriate rate based on the name in F2 and the date in G2. "T. Valko" wrote: 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? |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup based on a date being between a range
Biff - I keep getting errors when I click the "Post" button so I'm not sure
my last reply went through (it hasn't shown up yet)... so, once again, in a nutshell... THANK YOU VERY MUCH! "T. Valko" wrote: OK, try this: F2 = Mary G2 = 8/1/2008 =SUMPRODUCT(--(A2:A5=F2),--(B2:B5<=G2),--(C2:C5=G2),D2:D5) -- Biff Microsoft Excel MVP "CTEagle91" wrote in message ... Hi Biff - Thanks for such a quick reply! The table will always be sorted this way, but I'm not always looking for the most recent pay rate. For example, I want to put "Mary" in F2, and "08/01/08" in G2, and put some formula in H2 that will lookup the appropriate rate based on the name in F2 and the date in G2. "T. Valko" wrote: 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? |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup based on a date being between a range
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "CTEagle91" wrote in message ... Biff - I keep getting errors when I click the "Post" button so I'm not sure my last reply went through (it hasn't shown up yet)... so, once again, in a nutshell... THANK YOU VERY MUCH! "T. Valko" wrote: OK, try this: F2 = Mary G2 = 8/1/2008 =SUMPRODUCT(--(A2:A5=F2),--(B2:B5<=G2),--(C2:C5=G2),D2:D5) -- Biff Microsoft Excel MVP "CTEagle91" wrote in message ... Hi Biff - Thanks for such a quick reply! The table will always be sorted this way, but I'm not always looking for the most recent pay rate. For example, I want to put "Mary" in F2, and "08/01/08" in G2, and put some formula in H2 that will lookup the appropriate rate based on the name in F2 and the date in G2. "T. Valko" wrote: 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lookup to find the last value based on name and date field | Excel Discussion (Misc queries) | |||
| 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 | |||
Linear Mode
