Lookup between two dates

  • Thread starter Thread starter Nikkiv505
  • Start date Start date
N

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
 
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)
 
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)
 
Back
Top