Formula is not working in 1 of the following senario

[harman1213]

Dear Excel Experts,

I am create logic to build a formula. But failed in 1 condition out of 5.Need your help.

I have below 5 scenarios

Sl No	Scenarios	Remarks
1	Transaction_id and final_price2 is reflecting in the report	Matched
2	final_price2 > Gross Amount	Discount Issue
3	Transaction_id and final_price2 is not reflecting in the report	Not Matched
4	Duplicate transaction ID with Negative and positive gross amount in the report	Refund/cancelled case
5	Transaction pertains to the previous month/next month	Not considered

I am using this formula(=IF(TEXT(A2,"MMM")=$H$2,IF(C2=D2,"Matched",IF(D2="0","Not Matched",IF(AND(COUNTIF($B$2:$B$196,B2)>1,(C2<0)),"Refund",IF(AND(C2<D2,C2>-1),"Discount","Unknown Error")))),"Not Related to this month") )

But i am stuck in Refund case scenario where date is pertains to prior/next month. i am attaching sample data with formula.

 

[TASTEIN00]

Here you go. I had to adjust the dates to US settings for me to get it work, but the biggest correction I made was IF(TEXT(A2,"MMM")<>$H$2,"Not Related to This Month",IF(C2=D2,"Matched",IF(D2="0","Not Matched",IF(AND(COUNTIF($B$2:$B$196,B2)>1,(C2<0)),"Refund",IF(AND(C2<D2,C2>-1),"Discount","Unknown Error"))))).

You want to make sure that if it doesn't pertain to the month, then that's the only thing you want it to do. And then everything else will follow if it does equal.