Complex Look Up (amended)
 

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...

Complex Look Up (amended)
 

see your other post

--
Biff
Microsoft Excel MVP


"5F80YZ" wrote in message
...
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...

Complex Look Up (amended)
 

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)


--
Jacob


"5F80YZ" wrote:

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...