Formula to look for a match based on 2 criteria

hello,

I'm trying to create a formula that returns a value based on a match with 2 criteria. 1 of these criteria is based on wether a date falls within a date range but only if the other criteria is also met.

See example tables below. The formula should go in table 2. The formula should look for a row in table 1 where the 2 criteria are met:
-the ID number matches, and
-the date in one of the rows for the matching ID number falls within the given date range.
If this is the case the value in the last column of the row where the match occurs should be returned.

I've tried a number of different combinations of INDEX, MATCH and SUMPRODUCT, the difficulty seems to be that in table 1 the same ID can have multiple date ranges. Furthermore different ID's can have overlapping date ranges. The same ID will never have a duplicate date range though.

The formula that seems to come pretty close is:
{=INDEX($D$2:$D$8,MATCH(1,($A$2:$A$8=$A12)*(SUMPRODUCT(($B$2:$B$8<=C$11)*($C$2:$C$8>=C$11))=1),0))}

However it seems to return a value disregarding the ID number.

TABLE 1
ID Start date End date Code
A 22/08/2012 29/08/2012 ABC
B 22/07/2012 23/07/2012 DEF
B 18/09/2012 18/09/2012 ABC
B 25/09/2012 26/09/2012 ABC
C 13/06/2012 03/07/2012 XYZ
C 22/08/2012 24/08/2012 XYZ
C 24/09/2012 01/10/2012 ABC

TABLE 2
Date 21/07/2012 22/07/2012 23/07/2012 24/07/2012
A N/A N/A N/A N/A
B N/A DEF DEF N/A
C N/A N/A N/A N/A

TABLE above is showing the values that the formuka should return. The example formula will return the codes (ABC, DEF etc) in every row for dates 22/07 and 23/07 regardless of the ID number.

BTW, I'm using Excel 2010 on Windows 7.

Any suggestions on how to amedn the formula would be much appreciated!
Thanks.

this woudl be eaiser to work on with a workbook

Try this Array Formula (enter with Cntrl+Shft+Enter, not just Enter) in B12:

Formula:

Please Login or Register  to view this content.

Drag Down And Across

Hope this helps

you are welcome