Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Index and Match Formula
Hello,
I am having a few issues using the Index and Match formula in Excel. For example I can get the Index and Match formula working if the data is layed out like below: A B 1 01/01/06 0.7 2 07/01/06 0.8 3 14/01/06 0.9 i.e. Cell A1 is 01/06/06. Using the formula =(INDEX(A1:B3,MATCH("06/01/06",A1:A3,1)+1,1)) will return 0.8. Which is correct I want the next row down. How can I use this formula if the data is the other way round? A B C 1 01/01/06 07/01/06 14/01/06 2 0.7 0.8 0.9 I want to return the amount depending on the date, for example if the date is 06.01.06 I want to see 0.8. I tried a HLOOKUP but it returns the left hand side i.e. 0.7. Anyone offer any help? Thanks, Brett |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Index and Match Formula
try this
=INDEX(A1:C2,2,MATCH("06/01/06",A1:C1,1)+1) On Jun 5, 6:02*pm, Brett wrote: Hello, I am having a few issues using the Index and Match formula in Excel. For example I can get the Index and Match formula working if the data is layed out like below: * * * * * *A * * * * * *B 1 * *01/01/06 * * 0.7 2 * *07/01/06 * * 0.8 3 * *14/01/06 * * 0.9 i.e. Cell A1 is 01/06/06. Using the formula =(INDEX(A1:B3,MATCH("06/01/06",A1:A3,1)+1,1)) will return 0.8. *Which is correct I want the next row down. How can I use this formula if the data is the other way round? * * * * *A * * * * * * * * *B * * * * * * * C 1 * *01/01/06 * * *07/01/06 * * 14/01/06 2 * * *0.7 * * * * * *0.8 * * * * * * * * *0.9 I want to return the amount depending on the date, for example if the date is 06.01.06 I want to see 0.8. *I tried a HLOOKUP but it returns the left hand side i.e. 0.7. *Anyone offer any help? Thanks, Brett |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Index and Match Formula
On 5 June, 14:32, muddan madhu wrote:
try this =INDEX(A1:C2,2,MATCH("06/01/06",A1:C1,1)+1) On Jun 5, 6:02*pm, Brett wrote: Hello, I am having a few issues using the Index and Match formula in Excel. For example I can get the Index and Match formula working if the data is layed out like below: * * * * * *A * * * * * *B 1 * *01/01/06 * * 0.7 2 * *07/01/06 * * 0.8 3 * *14/01/06 * * 0.9 i.e. Cell A1 is 01/06/06. Using the formula =(INDEX(A1:B3,MATCH("06/01/06",A1:A3,1)+1,1)) will return 0.8. *Which is correct I want the next row down. How can I use this formula if the data is the other way round? * * * * *A * * * * * * * * *B * * * * * * * C 1 * *01/01/06 * * *07/01/06 * * 14/01/06 2 * * *0.7 * * * * * *0.8 * * * * * * * * *0.9 I want to return the amount depending on the date, for example if the date is 06.01.06 I want to see 0.8. *I tried a HLOOKUP but it returns the left hand side i.e. 0.7. *Anyone offer any help? Thanks, Brett- Hide quoted text - - Show quoted text - Thank you, this is perfect. You are a star. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index and Match Formula | Excel Worksheet Functions | |||
Need Help w/ Index and Match Formula | Excel Worksheet Functions | |||
index, match formula | Excel Worksheet Functions | |||
Formula using INDEX and MATCH | Excel Worksheet Functions | |||
How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions |