Vba to look if the first 4 numbers of a string matches the values in another column

[raphagcwill]

Hello!

Hope you guys doing great and perhaps can help me out.

What I have

In column C, starting from row 10 i have thousants of different combination of numbers “transaction numbers”

I column T, starting from row 2 i have a list with thousand of unique 4-digit numbers “client numbers”. Their names are present in the row to the right, column U

What I need

I would like VBA to match the first 4 digits of Column C (from C10 to lastrow) with the values in Column T.

If match is true, then copy the equivalent client name (found in column U) to column V

If match is false, then insert text “not our client” in column V

Thanks in advance!

 

[maabadi]

Try one of formula at column F or G:

Book1
	A	B	C	D	E	F	G	H
1
2	A111			A129824	B0001	B0003	B0019
3	A112			A121836	B0002	B0010	B0011
4	A113			A111630	B0003	B0016	B0001
5	A114			A124428	B0004	B0013	B0014
6	A115			A127353	B0005	B0007	B0017
7	A116			A126460	B0006	B0017	B0016
8	A117			A115363	B0007	B0018	B0005
9	A138			A119704	B0008		B0009
10	A119			A120600	B0009	B0008	B0010
11	A120			A112628	B0010	B0009	B0002
12	A121			A125799	B0011	B0002
13	A122			A128326	B0012	B0014	B0018
14	A123			A114253	B0013	B0015	B0004
15	A124			A122789	B0014	B0004	B0012
16	A145			A123514	B0015		B0013
17	A126			A113476	B0016	B0006	B0003
18	A127			A116248	B0017	B0005	B0006
19	A128			A117246	B0018	B0012	B0007
20	A129			A118517	B0019	B0001
21
22
Sheet3

Cell Formulas
Range	Formula
F2:F20	F2	=IFNA(INDEX($E$2:$E$20,MATCH(A2&"*",$D$2:$D$20,0)),"")
G2:G20	G2	=IFNA(INDEX($E$2:$E$20,MATCH(LEFT(D2,4),$A$2:$A$20,0)),"")

 

[raphagcwill]

thank you so much! That worked fine.

 

[Peter_SSs]

What I have
In column C, starting from row 10 i have thousants of different combination of numbers “transaction numbers”
I column T, starting from row 2 i have a list with thousand of unique 4-digit numbers “client numbers”. Their names are present in the row to the right, column U
What I need
I would like VBA to match the first 4 digits of Column C (from C10 to lastrow) with the values in Column T.
If match is true, then copy the equivalent client name (found in column U) to column V
If match is false, then insert text “not our client” in column V

Isn't this what you are describing?

21 10 24.xlsm
	C																S	T	U	V
1
2																		A111	Name 1	Name 1
3																		A112	Name 2	Name 2
4																		A113	Name 3	Name 3
5																		A114	Name 4	Name 4
6																		A115	Name 5	Name 5
7																		A116	Name 6	Name 6
8																		A117	Name 7	Name 7
9																		A138	Name 8	not our client
10	A129824																	A119	Name 9	Name 9
11	A121836																	A120	Name 10	Name 10
12	A111630																	A121	Name 11	Name 11
13	A124428																	A122	Name 12	Name 12
14	A127353																	A123	Name 13	Name 13
15	A126460																	A124	Name 14	Name 14
16	A115363																	A145	Name 15	not our client
17	A119704																	A126	Name 16	Name 16
18	A120600																	A127	Name 17	Name 17
19	A112628																	A128	Name 18	Name 18
20	A125799																	A129	Name 19	Name 19
21	A128326
22	A114253
23	A122789
24	A123514
25	A113476
26	A116248
27	A117246
28	A118517
29
Check Names

Cell Formulas
Range	Formula
V2:V20	V2	=IFNA(IF(MATCH(T2&"*",C$10:C$10000,0),U2),"not our client")

 

[raphagcwill]

Hello Peter!

Thank you very much for taking the time to help me.

It was exactly what I described. Your solution was spot on and i did not need to chance anything in the formula.

Have a great day!

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.