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

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

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.