Hi,

I have a table where I need to lookup a rate based on the employement date.
The lookup fields have a date from and date to and a rate. I want to lookup
the employment date and if it is between date from and date to give me the
rate. How would I go about doing that?
Help please!!
Here are an example of my data.
EMPLOYMENT DATE RATE
6/30/2003 ???
5/21/2001 ???
1/2/1986 ???
2/3/1994 ???

Date from Date To Rate
07/02/1982 09/04/1984 $26.61
09/05/1984 07/31/1985 $26.31
08/01/1985 07/31/1986 $26.01
08/01/1986 07/31/1987 $25.51
08/01/1987 07/31/1988 $25.01
08/01/1988 07/31/1989 $24.51
08/01/1989 07/31/1990 $24.01

With
A1:A8 containing your posted data: Date_From, Date_To, Rate
and
E1:F5 containing the data to look-up: Employment_Date, Rate

Since it doesnt look like you have any overlap in the Date_From column
Try this:
F2: =VLOOKUP(E2,$A$2:$C$8,3,1)

Copy that formula down as far as you need
Adjust range references to suit your situation.

Is that something you can work with?

Regards,

Ron Coderre
Microsoft MVP (Excel)

"Nikkiv505" wrote in message
...
Hi,

I have a table where I need to lookup a rate based on the employement
date.
The lookup fields have a date from and date to and a rate. I want to
lookup
the employment date and if it is between date from and date to give me the
rate. How would I go about doing that?
Help please!!
Here are an example of my data.
EMPLOYMENT DATE RATE
6/30/2003 ???
5/21/2001 ???
1/2/1986 ???
2/3/1994 ???

Date from Date To Rate
07/02/1982 09/04/1984 $26.61
09/05/1984 07/31/1985 $26.31
08/01/1985 07/31/1986 $26.01
08/01/1986 07/31/1987 $25.51
08/01/1987 07/31/1988 $25.01
08/01/1988 07/31/1989 $24.51
08/01/1989 07/31/1990 $24.01

Hi,

You can also try this

=sumproduct((A10:A16<=A2)*(B10:B16=A2),C10:C16). where A10:C16 holds the
rate table

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Nikkiv505" wrote in message
...
Hi,

I have a table where I need to lookup a rate based on the employement
date.
The lookup fields have a date from and date to and a rate. I want to
lookup
the employment date and if it is between date from and date to give me the
rate. How would I go about doing that?
Help please!!
Here are an example of my data.
EMPLOYMENT DATE RATE
6/30/2003 ???
5/21/2001 ???
1/2/1986 ???
2/3/1994 ???

Date from Date To Rate
07/02/1982 09/04/1984 $26.61
09/05/1984 07/31/1985 $26.31
08/01/1985 07/31/1986 $26.01
08/01/1986 07/31/1987 $25.51
08/01/1987 07/31/1988 $25.01
08/01/1988 07/31/1989 $24.51
08/01/1989 07/31/1990 $24.01

In Excel 2007...

To/From table in the range G1:I7

Dates to check in the range A2 on down.

Entered in B2 and copied down as needed:

=SUMIFS(I$1:I$7,H$1:H$7,"="&A2,G$1:G$7,"<="&A2)

--
Biff
Microsoft Excel MVP


"Nikkiv505" wrote in message
...
Hi,

I have a table where I need to lookup a rate based on the employement
date.
The lookup fields have a date from and date to and a rate. I want to
lookup
the employment date and if it is between date from and date to give me the
rate. How would I go about doing that?
Help please!!
Here are an example of my data.
EMPLOYMENT DATE RATE
6/30/2003 ???
5/21/2001 ???
1/2/1986 ???
2/3/1994 ???

Date from Date To Rate
07/02/1982 09/04/1984 $26.61
09/05/1984 07/31/1985 $26.31
08/01/1985 07/31/1986 $26.01
08/01/1986 07/31/1987 $25.51
08/01/1987 07/31/1988 $25.01
08/01/1988 07/31/1989 $24.51
08/01/1989 07/31/1990 $24.01