Lookup based on a date being between a 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)
 
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.
 
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
 
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?
 
OK, try this:

F2 = Mary
G2 = 8/1/2008

=SUMPRODUCT(--(A2:A5=F2),--(B2:B5<=G2),--(C2:C5>=G2),D2:D5)
 
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
 
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!
 
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!
 
Back
Top