Q: criteria based vlookup

[Mark]

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

 

[pinmaster]

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]

Hmmm... I haven't used array formulas that often. I'll have to test this out
first. If possible, could you give a one or two line summary of what your
formula does? somehow, I don't think it's what I'm looking for.

I probably didn't do a very good job explaining my question.

 

[pinmaster]

Hi,

I"m not an expert but I'll try as best I can.

the formula will look in column A for the value "AA" and look in column C
for the largest date that is less than the date to lookup and will return the
matching value in column B, since there's only one possible result SUMPRODUCT
should work just fine.

P.S. don't forget to enter the formula using Ctrl+Shift+Enter

Regards!
Jean-Guy

 

[pinmaster]

Hi,

Made a small adjustment to the formula, here's the latest version:

=SUMPRODUCT((E4:E7="aa")*(G4:G7=MAX(IF((G4:G7<DATE(2007,1,10))*(E4:E7="aa"),G4:G7))),F4:F7)

this formula will look in column C for the latest date that is less than the
lookup date that also matches "AA" in column A

HTH
Jean-Guy