Excel 2007 : Nested IF, Vlookup and Match, or?

I have a puzzle that I can't figure out how to approach it. I am sure it'll not be heard for Excel savvy, but...here the issue

Purpose: To populate Column C with Member Status Categories as Active, Not Active, New Member (sample file is attached)

Logic:
1. Active Members: If MCDate = Current date (int his case October 2011), then that member is active and all his/her previous consecutive months should also be populated as Active (as shown in column C
2. New Members: If a member joined in the current date (in this case October 2011) and has no previous records (based on Name and MCDATE), then the Member is "New Member"
3. Not Active Members: The easy logic would be all members who are not in Active or New categories will be assigned as Not Active

Anxious to hear some solutions,

Thank You,
HelalSample.xlsx

Helal,

Looks like you're using excel 2007, so the following should work for you:
=IF(COUNTIFS(B:B,$D$2,A:A,A2)>0,IF(COUNTIFS(B:B,"<"&$D$2,A:A,A2)>0,"Active","New"),"Inactive")

Try:

=IF(COUNTIFS($A$2:$A$31,A2,$B$2:$B$31,$D$2),IF(COUNTIF($A$2:$A$31,A2)=1,"New","Active"),"Not Active")

copied down