Formula is not working in 1 of the following senario

[Harmandeep]

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.

Formula.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Date	transcation_id_2	Gross Amount	Check	Diff	Duplicate Flag	Remarks
2	01/09/2022 21:09:43	ku5zqpJOWA0PTFrh-1	399	399	0	Not Duplicate	Matched	Sep	This Column is frezee for reference month
3	01/09/2022 21:09:43	I86wuyeOLFMhA9fj-1	699	699	0	Not Duplicate	Matched
4	30/09/2022 18:47:14	kbPguJpRvSKcG0HI-1	479	799	-320	Not Duplicate	Discount
5	30/09/2022 18:30:25	F5nsUeDGtMcZ6Cib-1	497	799	-302	Not Duplicate	Discount
6	30/09/2022 13:32:06	Rcg61BXV3AQSuE5e-1	699	0	699	Not Duplicate	Not Matched
7	30/09/2022 11:54:47	rnS9MYAmXPsoeqvK-1	699	0	699	Not Duplicate	Not Matched
8	30/09/2022 09:09:37	Rkj48v23BUDNZOxn-1	594	0	594	Not Duplicate	Not Matched
9	16/09/2022 21:10:59	2Rc0aOMCe8VJbnPr-1	99	99	0	Duplicate	Matched
10	22/09/2022 17:02:17	2Rc0aOMCe8VJbnPr-1	-99	99	-198	Duplicate	Refund
11	28/09/2022 08:53:32	eX8AGzLRIt53621B-1	699	699	0	Duplicate	Matched
12	04/10/2022 12:21:09	eX8AGzLRIt53621B-1	-699	699	-1398	Duplicate	Not Related to this month
13	30/08/2022 23:59:05	4SVMBjy5HszvKDAE-1	699	0	699	Not Duplicate	Not Related to this month
14	30/08/2022 23:57:51	VaIAUg8TKywXxreO-1	129	0	129	Not Duplicate	Not Related to this month
15	05/10/2022 00:01:45	UAE7dvX2jScoPuw0-1	699	0	699	Not Duplicate	Not Related to this month
16	05/10/2022 00:00:21	ik75eyHcUQBJtMlm-1	149	0	149	Not Duplicate	Not Related to this month
Report

Cell Formulas
Range	Formula
D2:D16	D2	=IFERROR(VLOOKUP(B2,Data!B:C,2,0),"0")
E2:E16	E2	=IFERROR(C2-D2,"")
F2:F16	F2	=IF(COUNTIF($B$2:$B$196,$B2)>1,"Duplicate","Not Duplicate")
G2:G16	G2	=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")