Help with index and match

[SandyC246]

I have a table that contains payment for several contracts between different dates, with contracts appearing on more than one line (but not with over lapping dates).

I'm trying to using index & match to sort these rows into one row for each contract. However when more than one contract is held in the original table the date range seems to be the minimum rather than all dates!

Example:
Table 1
Contract Start End Amount
0001 01/04/2014 25/04/2014 £120.00
0002 15/04/2014 25/04/2014 £250.00
0001 26/04/2014 15/05/2014 £120.00

Table 2
Contract 01/04/14 02/04/14 03/04/14 04/04/14 etc etc
0001
0002

Using index and match I can return the correct amounts under the correct dates for the correct contracts HOWEVER, in the example above, only the 15/04/14 to 25/04/14 will be shown - rather than all dates.

Any ideas anyone please as getting very frustrated.

Thank you

 

[BiocideJ]

Are you placing a 0 in the third argument of the MATCH() formula? E.g. MATCH(A1,B1:B10,0)

If not, that is likely the problem. If so, can you provide the exact formula you are using?

 

[SandyC246]

I've tried the formula two different ways:

=INDEX($H$2:$H$3,MATCH(B12,$A$2:$A$3,0),MATCH(D11,D2:D3,0),MATCH(D11,E2:E3,0))

and

=INDEX($H$2:$H$3,MATCH($A$15,IF(AND($C$2:$C$3<=D11,$D$2:$D$3>=D11),$A$2:$A$3),0))

 

[SandyC246]

Can anyone help as this is really starting to bug me now.

Thanking you in advance

 

[SandyC246]

I've tried the formula two different ways:

=INDEX($H$2:$H$3,MATCH(B12,$A$2:$A$3,0),MATCH(D11,D2:D3,0),MATCH(D11,E2:E3,0))

and

=INDEX($H$2:$H$3,MATCH($A$15,IF(AND($C$2:$C$3<=D11,$D$2:$D$3>=D11),$A$2:$A$3),0))