Hi,

Try this Array formula:

=SUMPRODUCT((A1:A100="aa")*(C1:C100=MAX(IF(C1:C100 <DATE(2007,1,15),C1:C100))),B1:B100)

enter using Ctrl+Shift+Enter

HTH
Jean-Guy
"Mark" wrote:

I have a table of codes, fees, and change dates such as below. The change
dates can happen at any point and the fees do not fall into any pattern. the
list is sorted by code ascending and change date ascending.

code fee change date
AA 10 1/1/2007
AA 15 1/15/2007
AA 12 2/20/2007
...
BB 5 1/1/2007
BB 10 1/23/2007
...


I have a list of records with codes and dates and have to lookup against
this list to find the lastest date change before the date on the record and
return the fee (ie. if the date on the record says 1/10/2007 for code AA, i'd
return the fee 10).

If there was only one code in the lookup, this would be simple. I'd just do
a vlookup(,,,FALSE) against the lookup table to return the fee. However,
since there are multiple codes, this isn't as easy.

Hopefully that makes sense. I'm looking for a solution *without* using VBA.
I have some code that does this already, but I figure it's worth a shot to
see whether this can be done through worksheet functions alone.

Thanks,
-Mark