# Formula is not working in 1 of the following senario



## Harmandeep (Dec 28, 2022)

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 NoScenariosRemarks1​Transaction_id  and final_price2 is reflecting in the reportMatched2​final_price2 > Gross AmountDiscount Issue3​Transaction_id and final_price2 is not reflecting in the reportNot Matched4​Duplicate transaction ID with Negative and positive gross amount in the reportRefund/cancelled case5​Transaction pertains to the previous month/next monthNot 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.


Formula.xlsxABCDEFGHIJKLM1Datetranscation_id_2Gross AmountCheckDiffDuplicate FlagRemarks201/09/2022 21:09:43ku5zqpJOWA0PTFrh-13993990Not DuplicateMatchedSepThis Column is frezee for reference month301/09/2022 21:09:43I86wuyeOLFMhA9fj-16996990Not DuplicateMatched430/09/2022 18:47:14kbPguJpRvSKcG0HI-1479799-320Not DuplicateDiscount530/09/2022 18:30:25F5nsUeDGtMcZ6Cib-1497799-302Not DuplicateDiscount630/09/2022 13:32:06Rcg61BXV3AQSuE5e-16990699Not DuplicateNot Matched730/09/2022 11:54:47rnS9MYAmXPsoeqvK-16990699Not DuplicateNot Matched830/09/2022 09:09:37Rkj48v23BUDNZOxn-15940594Not DuplicateNot Matched916/09/2022 21:10:592Rc0aOMCe8VJbnPr-199990DuplicateMatched1022/09/2022 17:02:172Rc0aOMCe8VJbnPr-1-9999-198DuplicateRefund1128/09/2022 08:53:32eX8AGzLRIt53621B-16996990DuplicateMatched1204/10/2022 12:21:09eX8AGzLRIt53621B-1-699699-1398DuplicateNot Related to this month1330/08/2022 23:59:054SVMBjy5HszvKDAE-16990699Not DuplicateNot Related to this month1430/08/2022 23:57:51VaIAUg8TKywXxreO-11290129Not DuplicateNot Related to this month1505/10/2022 00:01:45UAE7dvX2jScoPuw0-16990699Not DuplicateNot Related to this month1605/10/2022 00:00:21ik75eyHcUQBJtMlm-11490149Not DuplicateNot Related to this monthReportCell FormulasRangeFormulaD2:D16D2=IFERROR(VLOOKUP(B2,Data!B:C,2,0),"0")E2:E16E2=IFERROR(C2-D2,"")F2:F16F2=IF(COUNTIF($B$2:$B$196,$B2)>1,"Duplicate","Not Duplicate")G2:G16G2=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")


----------

