Edit formula to show one match only

[RAJESH1960]

Hello guys
With the formula shared by mikerickson, I have been able to get all the matched rows. What mikerickson, shared was what I asked for. In one of the other data, I came across this issue. Hence, this is a different query, since I faced an issue here.
When the Portal rows have more than 1 row of the same amount and Tally has only one row, then it shows all the rows as matched. Likewise, when the Tally rows has more than 1 row of the same amount and Portal has only one row, then too -it shows all the rows as matched. I need help to edit this formula in such a way that the formula will accept only one row in each case and show the remaining as #N/A.

Query Select one in Multiple common Amount match formula.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Line	As Per	ID NO.	A	B	C	Gross	Taxable	IGST	CGST	SGST	Formula	Actual
2	1	PORTAL	27XBJXS4065R1XQ				873.00	740.00	133.20	-	-	Matched
3	2	PORTAL	27XXXCI7904G1XN				1,069.08	906.00	163.08	-	-	#N/A	Mismatch
4	3	PORTAL	29XECXB6064X1X5				2,596.00	2,200.00	-	198.00	198.00	#N/A	Mismatch
5	4	PORTAL	07XXQFK0196B1XD				3,587.00	3,040.00	547.20	-	-	Matched
6	5	PORTAL	27XBJXS4065R1XQ				6,018.00	5,100.00	918.00	-	-	Matched
7	6	PORTAL	27XBJXS4065R1XQ				6,018.00	5,100.00	918.00	-	-	Matched	Mismatch
8	7	PORTAL	27XBJXS4065R1XQ				6,018.00	5,100.00	918.00	-	-	Matched	Mismatch
9	8	PORTAL	27XBJXS4065R1XQ				6,018.00	5,100.00	918.00	-	-	Matched	Mismatch
10	9	PORTAL	19XFSXX1499K1XF				6,490.00	5,500.00	990.00	-	-	Matched
11	10	PORTAL	19XXCCX2016R1XI				6,490.00	5,500.00	990.00	-	-	#N/A	Mismatch
12	11	PORTAL	27XBJXS4065R1XQ				8,201.00	6,950.00	1,251.00	-	-	Matched
13	12	PORTAL	27XBJXS4065R1XQ				8,260.00	7,000.00	1,260.00	-	-	#N/A	Mismatch
14	13	PORTAL	27XBJXS4065R1XQ				9,411.00	7,975.00	1,435.50	-	-	#N/A	Mismatch
15	14	TALLY	27XBJXS4065R1XQ				873.00	740.00	133.20			Matched
16	15	TALLY	27XBJXS4065R1XQ				873.00	740.00	133.20			Matched	Mismatch
17	16	TALLY	29XDDXK6496J1XX				2,596.00	2,200.00		198.00	198.00	#N/A	Mismatch
18	17	TALLY	07XXQFK0196B1XD				3,587.00	3,040.00	547.20			Matched
19	18	TALLY	27XBJXS4065R1XQ				6,018.00	5,100.00	918.00			Matched
20	19	TALLY	19XFSXX1499K1XF				6,490.00	5,500.00	990.00			Matched
21	20	TALLY	27XBJXS4065R1XQ				7,571.00	6,416.00	1,154.88			#N/A	Mismatch
22	21	TALLY	27XBJXS4065R1XQ				8,201.00	6,950.00	1,251.00			Matched	Mismatch
23	22	TALLY	27XBJXS4065R1XQ				8,201.00	6,950.00	1,251.00			Matched	Mismatch
24	23	TALLY	27XBJXS4065R1XQ				8,201.00	6,950.00	1,251.00			Matched	Mismatch
Match All

Cell Formulas
Range	Formula
L2:L24	L2	=IF(MATCH(1,((ABS(G2-$G$2:$G$23018)<=1))*(C2=$C$2:$C$23018)*(B2<>$B$2:$B$23018),0),"Matched","")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[RAJESH1960]

Just going through the old records, I found a similar issue which was solved. If I edit the formula and add G$2:G2,G3)+1)), maybe it will work. But where to add no idea.?
That query had IF, IFERROR, INDEX, COUNTIFS all included in one formula.

 

[RAJESH1960]

Just going through the old records, I found a similar issue which was solved. If I edit the formula and add G$2:G2,G3)+1)), maybe it will work. But where to add no idea.?
That query had IF, IFERROR, INDEX, COUNTIFS all included in one formula.

As I was unable to get any response, I have shared this post in another forum.

 

[Fluff]

I have shared this post in another forum.

Thanks for letting us know, but can you please also post a link to your question on the other forum. Thanks

 

[RAJESH1960]

Thanks for letting us know, but can you please also post a link to your question on the other forum. Thanks

Share link
Need to edit formula to get one match

 

[RAJESH1960]

fyi, the above query is solved.