Lookup between two dates

[Nikkiv505]

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

 

[Ron Coderre]

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 doesn’t 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)

 

[Ashish Mathur]

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

 

[T. Valko]

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)