Lookup and Find a value from a date range

[Pumba]

Hi There

I have a list of various exchange rates by currency and want to find where my transaction meets the exchange rate criteria. The data is as follows:

Currency Start Date of rate Exchange Rate
GBP 116.6
GBP 01/03/2011 117.321
GBP 01/04/2011 114
GBP 01/05/2011 115
GBP 01/06/2011 116
CAD 73.26
CAD 01/03/2011 74.32
CAD 18/04/2011 80


I then have a transaction which occurs on 05/05/20011 in the currency of GBP so it should pick up the exchange rate of 115. All my records have one blank value in the start date symbolising the start of the exchange rate.

I can pick up GBP by using a VLookup but how do I then tell it to pick up the date based upon a range found in my exchange rate table combined with the fact there is one blank value as default.

Thanks

 

[njimack]

Assuming your data is in the range A2:C9, F1 = "GBP", F2 = 05/05/11, use the following formula, confirmed with ctrl+shift+enter:

=SUMPRODUCT(--(A2:A9=F1),--(B2:B9=MAX(IF(A2:A9=F1,IF(B2:B9<F2,B2:B9)))),--(C2:C9))

 

[konew1]

If you prefer to avoid a CSE formula you could use
=SUMPRODUCT(--(A2:A9=F1),--(B2:B9 >(F2-DAY(F2))),--(B2:B9<= EOMONTH(F2,0)),--(C2:C9))