Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
complex color fill conditions- if statements or complex formula? | Excel Discussion (Misc queries) | |||
Problem with an amended macro. Please help! | Excel Discussion (Misc queries) | |||
SUMPRODUCT - (amended) Exclude LAST Row of Matched Criteria (Month & Year) | Excel Worksheet Functions | |||
Complex Index Match Help (or at least complex to me) | Excel Discussion (Misc queries) | |||
Pls disregard last post used the second amended suggestion from John | Charts and Charting in Excel |