Complicated use of INDEX and MATCH to lookup multiple columns

[cHaynes]

Hi, I am trying to do a version of the formula given for method 1 here:
http://support.microsoft.com/kb/214142

Using Excel 2007, I have to retrieve a value in column D of sheet Media by
matching the value in sheet Media column A to sheet Promo column A and also
checking that the dates in sheet Media columns B and C overlap with the dates
in sheet Promo columns D and E.

I get an #N/A error for a value/values that should be found when using the
following formula:

{=INDEX(Media!$D$2:$D$4473,MATCH(Promo!A2,IF(OR(AND(Media!$C$2:$C$4473>=Promo!D2,Media!$C$2:$C$4473<=Promo!E2),AND(Media!$B$2:$B$4473>=Promo!D2,Media!$B$2:$B$4473<=Promo!E2),AND(Media!$B$2:$B$4473<Promo!D2,Media!$C$2:$C$4473>Promo!$E$2:$E$6670)),Media!$A$2:$A$4473),0))}

If anyone could explain why this is not working I would be hugely grateful.
Thanks for your time
Craig

 

[cHaynes]

Sorry to double post but after some more experimentation I've found that a
simplified version of the date comparison works. Any time I use 'AND'
however, it breaks again. This is the version with just an OR...

{=INDEX(Media!$D$2:$D$4473,MATCH(Promo!A2,IF(OR(Media!$B$2:$B$4473>=Promo!D2,Media!$C$2:$C$4473<=Promo!E2),Media!$A$2:$A$4473),0))}