Assuming your Transaction Dates are in column A, the Effective Dates are in
column D, and the published rates are in column E (all with headers as
shown), then the formula to get the rate for a particular date (which you
would put in column B starting at cell B2) is:

=VLOOKUP($A2,$D$2:$E$11,2)

Drag this down as far as you have Transaction Dates.

Note that VLookup will find the item that is closest but SMALLER than the
item you are searching for, so in this case it finds the Effective Date that
is less than or equal to the Transaction Date. This seems to be the correct
way of looking up the rates - they are good from the time they are published
until the date a new rate is published.

HTH,

Eric


"Cathy Landry" wrote:

Hello,

I run a monthly mileage report that contains transaction date, this date
determines the rate per mile our company reimburses employees. Is there a
way via VBA to pull the correct rate based on trans dt? The example below
shows the trans date and rate based on our company's rate.



TRANS DATE RATE EFF DT RATE
6/15/2009 0.46 1/1/2005 0.405
5/12/2009 0.45 9/1/2005 0.485
9/2/2005 0.485 1/1/2006 0.445
2/8/2009 0.55 1/1/2007 0.485
3/10/2009 0.45 1/1/2008 0.505
3/9/2009 0.55 7/1/2008 0.585
1/1/2009 0.55
3/10/2009 0.45
6/1/2009 0.46
7/1/2009 0.48

Thank you in advance :)
Cathy