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?

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?

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?

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?

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?

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?

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?

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?

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?

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?

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?

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?