Complex Look Up (amended)

[5F80YZ]

Hi. I desparately need help with this look up function

The look up data is as below

Sheet 1
ColumnA Column B Column C

Start Date End Date Exchange Rate
26/10/09 25/11/09 1.123
26/11/09 25/12/09 1.654
26/12/09 25/01/09 1.897

Sheet 2
Column A Column B

Date Exchange Rate
05/11/09 ________
15/01/09 ________
21/12/09 ________

OK, what I need to do is in Sheet 2, I need to look up for the dates in
column A and get the look up value from Column C is Sheet 1. For eg, the 1st
one is the 5/11/09 so the exchange rate will be 1.123 (since its between
26/10/09 - 25/11/09). Can someone please help me by suggesting what formula I
should use to pick up the exchange rate from the dates in Sheet 1...

 

[T. Valko]

see your other post

 

[Jacob Skaria]

If data in Sheet1 is sorted in ascending order and if there is a match try
=LOOKUP(A2,Sheet1!$A$2:$A$10,Sheet1!$C$2:$C$10)

OR else try

=SUMPRODUCT((Sheet1!$A$1:$A$100<=A2)*(Sheet1!$B$1:$B$100>=A2),
Sheet1!$C$1:$C$100)