Match value which is a part of a whole value

[RAJESH1960]

Hello formula experts
I have a very challenging formula to write and I need your expertise to write it.
All the entries in this sheet are actually matched. Since the invoice numbers in tally rows is a part of the invoice number of Portal row, which happens in more than 90% of the cases, the formula is showing an invoice No. Mismatch in column K. I am not sure whether it is even possible to match a part of the Invoice number with another invoice number, but I want to give it a try. Formula to be something like this....
Search for the value in a cell of Tally row in Portal row whether it is in the beginning, in the middle or at the end of the value but in one sequences than treat it as a match. Can anyone help me to write this formula in column J and get the result as Matched.?

Get part of Invoice nu to match.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Line	As Per	GSTIN	Name	Invoice No.	Date	IGST	CGST	SGST	Remarks	Invoice No. Mismatch
2	5	PORTAL	06AAVPK7054P1ZT		6702002102000013	26-05-2022				MATCHED	Invoice No. Mismatch
3	6	TALLY	06AAVPK7054P1ZT		13	27-05-2022				MATCHED	Invoice No. Mismatch
4	7	PORTAL	06AAVPK7054P1ZT		G/364	03-05-2022				MATCHED	Invoice No. Mismatch
5	8	TALLY	06AAVPK7054P1ZT		364	04-05-2022				MATCHED	Invoice No. Mismatch
6	1	PORTAL	06AARPP9088L1ZM		000220/2022-23	20-06-2022				MATCHED	Invoice No. Mismatch
7	2	TALLY	06AARPP9088L1ZM		220	20-06-2022				MATCHED	Invoice No. Mismatch
8	9	PORTAL	29AAACP0165G1ZL		M220430003401710	30-04-2022				MATCHED	Invoice No. Mismatch
9	10	TALLY	29AAACP0165G1ZL		3401710	30-04-2022				MATCHED	Invoice No. Mismatch
10	13	PORTAL	29AAACS9735K1ZR		GST-186-2022-23	29-07-2022				MATCHED	Invoice No. Mismatch
11	14	TALLY	29AAACS9735K1ZR		186	29-07-2022				MATCHED	Invoice No. Mismatch
12	15	TALLY	29AAACS9735K1ZR		756	14-05-2022				MATCHED	Invoice No. Mismatch
13	16	PORTAL	29AAACS9735K1ZR		756/22-23	14-05-2022				MATCHED	Invoice No. Mismatch
14	23	PORTAL	29AAACS9735K1ZR		PNP/I/199	28-05-2022				MATCHED	Invoice No. Mismatch
15	24	TALLY	29AAACS9735K1ZR		199	28-05-2022				MATCHED	Invoice No. Mismatch
16	29	PORTAL	29AAACS9735K1ZR		925/22-23	25-05-2022				MATCHED	Invoice No. Mismatch
17	30	PORTAL	29AAACS9735K1ZR		1080/22-23	02-06-2022				MATCHED	Invoice No. Mismatch
18	32	TALLY	29AAACS9735K1ZR		925	25-05-2022				MATCHED	Invoice No. Mismatch
19	33	TALLY	29AAACS9735K1ZR		1080	02-06-2022				MATCHED	Invoice No. Mismatch
20	17	PORTAL	29AAACS9735K1ZR		TAX/02432/22-23	21-05-2022				MATCHED	Invoice No. Mismatch
21	18	TALLY	29AAACS9735K1ZR		2432	22-05-2022				MATCHED	Invoice No. Mismatch
22	21	PORTAL	29AAACS9735K1ZR		173/22-23	12-04-2022				MATCHED	Invoice No. Mismatch
23	22	TALLY	29AAACS9735K1ZR		173	13-04-2022				MATCHED	Invoice No. Mismatch
24	31	PORTAL	29AAACS9735K1ZR		1434/22-23	22-06-2022				MATCHED	Invoice No. Mismatch
25	34	TALLY	29AAACS9735K1ZR		1434	23-06-2022				MATCHED	Invoice No. Mismatch
26	3	PORTAL	06AARPP9088L1ZM		R-4/2022-23	04-07-2022				MATCHED	Invoice No. Mismatch
27	4	TALLY	06AARPP9088L1ZM		23	04-07-2022				MATCHED	Invoice No. Mismatch
28	11	PORTAL	29AAACS9735K1ZR		1468/22-23	24-06-2022				MATCHED	Invoice No. Mismatch
29	12	TALLY	29AAACS9735K1ZR		1468	24-06-2022				MATCHED	Invoice No. Mismatch
30	19	PORTAL	29AAACS9735K1ZR		264/22-23	18-04-2022				MATCHED	Invoice No. Mismatch
31	20	TALLY	29AAACS9735K1ZR		264	18-04-2022				MATCHED	Invoice No. Mismatch
32	25	PORTAL	29AAACS9735K1ZR		808/22-23	18-05-2022				MATCHED	Invoice No. Mismatch
33	26	TALLY	29AAACS9735K1ZR		808	18-05-2022				MATCHED	Invoice No. Mismatch
34	27	PORTAL	29AAACS9735K1ZR		1322/22-23	16-06-2022				MATCHED	Invoice No. Mismatch
35	28	TALLY	29AAACS9735K1ZR		1322	16-06-2022				MATCHED	Invoice No. Mismatch
Matched

Cell Formulas
Range	Formula
K2:K35	K2	=IF(COUNTIFS($B$2:$B$20000,IF($B2="PORTAL","TALLY","PORTAL"),$C$2:$C$20000,$C2,$E$2:$E$20000,$E2)>0,"MATCHED","Invoice No. Mismatch")

 

[cmowla]

How about this? (This assumes that the one MISMATCH is actually a mismatch . . . that you made a typo with that one. It doesn't follow the general "pattern" either. And its invoice number should be 4.) And I made the assumption that with invoice number's like "6702002102000013" (without any "/" and "-" and any letters), that the invoice number begins if after at least 3 consecutive 0's. And that it begins with a digit other than 0. (And the orange helper columns calculate these types of invoice no.'s and the green calculates all other types.)

And, although I could put this in one formula, since the LET function is not in Office 2019, I don't think that would be a good idea!

Blank.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Line	As Per	GSTIN	Name	Invoice No.	Date	IGST	CGST	SGST	Remarks		/	-
2	1	PORTAL	06AARPP9088L1ZM		000220/2022-23	20-06-2022				MATCHED with TALLY on Line 2		7	12	1	3	220/2022-23	4	7	220	220
3	2	TALLY	06AARPP9088L1ZM		220	20-06-2022						0	0	0	0		1	3	220
4	3	PORTAL	06AARPP9088L1ZM		R-4/2022-23	04-07-2022				NOT MATCHED!	Invoice No. Mismatch: 4	4	2	0	0		3	4	4	4
5	4	TALLY	06AARPP9088L1ZM		23	04-07-2022						0	0	0	0		1	2	23
6	5	PORTAL	06AAVPK7054P1ZT		6702002102000013	26-05-2022				MATCHED with TALLY on Line 6		0	0	11	4	13	1	16	6702002102000013	13
7	6	TALLY	06AAVPK7054P1ZT		13	27-05-2022						0	0	0	0		1	2	13
8	7	PORTAL	06AAVPK7054P1ZT		G/364	03-05-2022				MATCHED with TALLY on Line 8		2	0	0	0		3	5	364	364
9	8	TALLY	06AAVPK7054P1ZT		364	04-05-2022						0	0	0	0		1	3	364
10	9	PORTAL	29AAACP0165G1ZL		M220430003401710	30-04-2022				MATCHED with TALLY on Line 10		0	0	7	3	3401710	2	16	220430003401710	3401710
11	10	TALLY	29AAACP0165G1ZL		3401710	30-04-2022						0	0	0	0		1	7	3401710
12	11	PORTAL	29AAACS9735K1ZR		1468/22-23	24-06-2022				MATCHED with TALLY on Line 12		5	8	0	0		1	5	1468	1468
13	12	TALLY	29AAACS9735K1ZR		1468	24-06-2022						0	0	0	0		1	4	1468
14	13	PORTAL	29AAACS9735K1ZR		GST-186-2022-23	29-07-2022				MATCHED with TALLY on Line 14		0	4	0	0		5	8	186	186
15	14	TALLY	29AAACS9735K1ZR		186	29-07-2022						0	0	0	0		1	3	186
16	15	TALLY	29AAACS9735K1ZR		756	14-05-2022						0	0	0	0		1	3	756
17	16	PORTAL	29AAACS9735K1ZR		756/22-23	14-05-2022				MATCHED with TALLY on Line 15		4	7	0	0		1	4	756	756
18	17	PORTAL	29AAACS9735K1ZR		TAX/02432/22-23	21-05-2022				MATCHED with TALLY on Line 18		4	13	0	0		6	10	2432	2432
19	18	TALLY	29AAACS9735K1ZR		2432	22-05-2022						0	0	0	0		1	4	2432
20	19	PORTAL	29AAACS9735K1ZR		264/22-23	18-04-2022				MATCHED with TALLY on Line 20		4	7	0	0		1	4	264	264
21	20	TALLY	29AAACS9735K1ZR		264	18-04-2022						0	0	0	0		1	3	264
22	21	PORTAL	29AAACS9735K1ZR		173/22-23	12-04-2022				MATCHED with TALLY on Line 22		4	7	0	0		1	4	173	173
23	22	TALLY	29AAACS9735K1ZR		173	13-04-2022						0	0	0	0		1	3	173
24	23	PORTAL	29AAACS9735K1ZR		PNP/I/199	28-05-2022				MATCHED with TALLY on Line 24		4	0	0	0		7	9	199	199
25	24	TALLY	29AAACS9735K1ZR		199	28-05-2022						0	0	0	0		1	3	199
26	25	PORTAL	29AAACS9735K1ZR		808/22-23	18-05-2022				MATCHED with TALLY on Line 26		4	7	0	0		1	4	808	808
27	26	TALLY	29AAACS9735K1ZR		808	18-05-2022						0	0	0	0		1	3	808
28	27	PORTAL	29AAACS9735K1ZR		1322/22-23	16-06-2022				MATCHED with TALLY on Line 28		5	8	0	0		1	5	1322	1322
29	28	TALLY	29AAACS9735K1ZR		1322	16-06-2022						0	0	0	0		1	4	1322
30	29	PORTAL	29AAACS9735K1ZR		925/22-23	25-05-2022				MATCHED with TALLY on Line 32		4	7	0	0		1	4	925	925
31	30	PORTAL	29AAACS9735K1ZR		1080/22-23	02-06-2022				MATCHED with TALLY on Line 33		5	8	0	0		1	5	1080	1080
32	31	PORTAL	29AAACS9735K1ZR		1434/22-23	22-06-2022				MATCHED with TALLY on Line 34		5	8	0	0		1	5	1434	1434
33	32	TALLY	29AAACS9735K1ZR		925	25-05-2022						0	0	0	0		1	3	925
34	33	TALLY	29AAACS9735K1ZR		1080	02-06-2022						0	0	0	0		1	4	1080
35	34	TALLY	29AAACS9735K1ZR		1434	23-06-2022						0	0	0	0		1	4	1434
Sheet1

Cell Formulas
Range	Formula
J2:J35	J2	=IF(IFERROR(MATCH(T2,E:E,0),-1)<>-1,"MATCHED with TALLY on Line "&INDEX(A:A,MATCH(T2,E:E,0),1),IF(B2="PORTAL","NOT MATCHED!",""))
K2:K35	K2	=IF(AND(B2="PORTAL",J2="NOT MATCHED!"),"Invoice No. Mismatch: "&T2,"")
L2:L35	L2	=IFERROR(SEARCH("/",E2),0)
M2:M35	M2	=IFERROR(SEARCH("-",E2),0)
N2:N35	N2	=MAX(IFERROR(SEARCH(REPT("0",SEQUENCE(1,LEN(E2),3)),E2,1),0))
O2:O35	O2	=MAX(IF(IFERROR(SEARCH(REPT("0",SEQUENCE(1,LEN(E2),3)),E2,1),0)<>0,LEN(REPT("0",SEQUENCE(1,LEN(E2),3))),0))
P2:P35	P2	=IFERROR(MID(E2,N2+O2,LEN(E2)),"")
Q2:Q35	Q2	=MIN(IF(IFERROR(IF(VALUE(MID(E2,SEQUENCE(1,LEN(E2),1),1))>0,VALUE(MID(E2,SEQUENCE(1,LEN(E2),1),1)),-1),-1)>-1,SEQUENCE(1,LEN(E2),1),LEN(E2)))
R2:R35	R2	=MIN(IFERROR(Q2+VALUE(IFERROR("|"&VALUE(MID(E2,SEQUENCE(1,LEN(E2),Q2+1),1)),SEQUENCE(1,LEN(E2),1))),LEN(E2)))
S2:S35	S2	=IF(IFERROR(VALUE(RIGHT(MID(E2,Q2,R2-Q2+1),1)),-1)=-1,LEFT(MID(E2,Q2,R2-Q2+1),LEN(MID(E2,Q2,R2-Q2+1))-1),MID(E2,Q2,R2-Q2+1))
T2:T35	T2	=IF(AND(L2=0,M2=0),P2,S2)

 

[RAJESH1960]

Wow!! This was unexpected. You have been busy with the query and must have spent a lot of time trying to solve this. Thank you very much cmowla.
To be frank with you, it is very complicated and I didn't understand any part of it as it is beyond my capacity to understand it. But I will go through it and try to understand and learn something new and interesting.
I was just expecting a formula to enter in the remarks column and get all the rows as Matched.
Can you please the sheet where you have entered various formulas.? I don't see an option to copy or download it.

 

[cmowla]

You have been busy with the query and must have spent a lot of time trying to solve this.

I just spent about 2 hours (tops). I never did something like this before, to be honest.

Can you please the sheet where you have entered various formulas.? I don't see an option to copy or download it.

Just copy the formulas (one at a time) that are listed in the table Cell Formulas into the cell address in bold font (in your sheet). Once you have done that, carry (drag) all formulas down.

And, you're quite welcome!