Vlooup workin very randomly

[toony]

Hi. First, I hope that I pasted the below spreadsheet correctly from the LSBB adding as I can only see codes but I suppose it will convert into a table once posted?
Well, I have used a normal vlookup formula but it seems to work very randomly and can't understand why. THe format is general and both the lookup value and array table are only numbers.
If you can help me please

test2 ....xlsx
	A	B	C	D	E	F	G
1	Component				part	koa part	TABLE ARRAY
2	1015073	#N/A			1015099	RK73B2BTTDD330J	20000
3	1015099	#N/A			1015357	RK73Z2BTTD	10000
4	1015357	#N/A			1023088	RK73B2BTTD472J	5000
5	1015776	#N/A			1024425	RK73B1JTTD100J	5000
6	1016099	#N/A			1024473	RK73B1JTTDD102J	10000
7	1016175	#N/A			1024505	RK73B1JTTDD223J	10000
8	1022224	#N/A			1024517	RK73B1JTTD683J	5000
9	1023020	#N/A			1024529	RK73B1JTTD224J	5000
10	1023088	#N/A			1026484	RK73H2ATTD2001F	15000
11	1023253	#N/A			1026599	RK73H2ATTD3162F	5000
12	1023261	#N/A			1026616	RK73H2ATTD4752F	5000
13	1023298	#N/A			1029590	RK73H1JTTD2000F	5000
14	1023380	#N/A			1029629	RK73H1JTTD5110F	10000
15	1023398	#N/A			1029751	RK73H1JTTD9531F	5000
16	1023414	#N/A			1029849	RK73H1JTTDB1003F	80000
17	1023543	#N/A			152846-11	RK73H1JTTD1272F	15000
18	1024401	#N/A			153073-6	RK73H2ATTD8200F	35000
19	1024425	#N/A			153473-35	RK73B1ETTPD270J	20000
20	1024435	#N/A			153473-37	RK73B1ETTP330J	30000
21	1024452	#N/A			153473-49	RK73B1ETTP101J	30000
22	1024473	#N/A			153473-56	RK73B1ETTP201J	10000
23	1024484	#N/A			153473-81	RK73B1ETTP222J	20000
24	1024505	#N/A			153473-89	RK73B1ETTP472J	40000
25	1024508	#N/A			153474-1	RK73B1ETTP103J	20000
26	1024513	#N/A			153474-41	RK73B1ETTP474J	50000
27	1024517	#N/A			153758-9	RK73B1JTTD2R2J	20000
28	1024520	#N/A			153758-92	RK73B1JTTD622J	10000
29	1024529	#N/A			153760-41	RK73B2BTTD470J	5000
30	1024538	#N/A			153905-66	RK73H1ETTP4750F	10000
31	1025913	#N/A			153905-85	RK73H1ETTP7500F	10000
32	1026359	#N/A			153905-89	RK73H1ETTP8250F	20000
33	1026376	#N/A			153905-94	RK73H1ETTP9310F	10000
34	1026443	#N/A			153906-18	RK73H1ETTP1501F	40000
35	1026472	#N/A			153906-33	RK73H1ETTP2151F	10000
36	1026481	#N/A			153906-52	RK73H1ETTP3401F	20000
37	1026484	#N/A			153906-55	RK73H1ETTP3651F	10000
38	1026486	#N/A			153906-68	RK73H1ETTP4991F	10000
39	1026487	#N/A			153906-71	RK73H1ETTP5361F	10000
40	1026497	#N/A			153906-72	RK73H1ETTP5491F	20000
41	1026547	#N/A			153907-1	RK73H1ETTPD1002F	340000
42	1026568	#N/A			153907-27	RK73H1ETTP1872F	10000
43	1026599	#N/A			153907-30	RK73H1ETTP2002F	50000
44	1026616	#N/A			153907-39	RK73H1ETTP2492F	30000
45	1026647	#N/A			153907-46	RK73H1ETTP2942F	10000
46	1029561	#N/A			153907-47	RK73H1ETTP3012F	10000
47	1029590	#N/A			153907-49	RK73H1ETTP3162F	20000
48	1029603	#N/A			153907-56	RK73H1ETTP3742F	10000
49	1029619	#N/A			153907-73	RK73H1ETTP5622F	10000
50	1029620	#N/A			153907-79	RK73H1ETTP6492F	10000
51	1029629	#N/A			153908-1	RK73H1ETTP1003F	70000
52	1029657	#N/A			153908-68	RK73H1ETTP4993F	20000
53	1029677	#N/A			153908-9	RK73H1ETTP1213F	10000
54	1029683	#N/A			154108-1	RK73H1ETTP1004F	10000
55	1029686	#N/A			154110-3	RK73H1JTTD2202F	60000
56	1029689	#N/A			154112-1	RK73Z1ETTPD	400000
57	1029691	#N/A			154383-34	RK73H2BTTD22R1F	5000
58	1029693	#N/A			154383-66	RK73H2BTTD47R5F	5000
59	1029695	#N/A			154384-38	RK73H2BTTD2430F	10000
60	1029696	#N/A			154468-4	SR732BTTDR110F	5000
61	1029698	#N/A			154486-134	RK73H2ATTD24R3F	15000
62	1029700	#N/A			154486-273	RK73H2ATTD6810F	25000
63	1029707	#N/A			154592-24	RK73H1ETTP2700F	10000
64	1029708	#N/A			154592-25	RK73H1ETTP3000F	10000
65	1029714	#N/A			154592-31	RK73H1ETTP5100F	10000
66	1029715	#N/A			154592-35	RK73H1ETTP8200F	20000
67	1029722	#N/A			154592-41	RK73H1ETTP2401F	10000
68	1029725	#N/A			154592-42	RK73H1ETTP2701F	10000
69	1029728	#N/A			154592-51	RK73H1ETTP6201F	20000
70	1029731	#N/A			154592-55	RK73H1ETTP1202F	10000
71	1029736	#N/A			154592-66	RK73H1ETTP4702F	50000
72	1029741	#N/A			154592-85	RK73H1ETTP5103F	20000
73	1029751	#N/A			154593-3	RK73H1JTTD18R0F	5000
74	1029753	#N/A			154593-40	RK73H1JTTD2201F	10000
75	1029757	#N/A			154593-48	RK73H1JTTD4701F	20000
76	1029764	#N/A			154593-49	RK73H1JTTD5101F	15000
77	1029765	#N/A			154593-56	RK73H1JTTD1602F	15000
78	1029766	#N/A			154593-60	RK73H1JTTD2702F	15000
79	1029768	#N/A			154593-66	RK73H1JTTDD4702F	10000
80	1029771	#N/A			155343-1	RK73H2ETTD60R4F	5000
81	1029778	#N/A			155565-3	WK73R2JTTE242J	4000
82	1029782	#N/A			155915-1	RK73Z2ATTDD	30000
83	1029789	#N/A			156881-1	SR732ATTDR10J	15000
84	1029791	#N/A			200314-1	RK73Z1JTTDD	190000
85	1029799	#N/A			201219-35	RK73B2HTTE271J	8000
86	1029804	#N/A			34114484A	RK73B1ETTP103J	600000
87	1029815	#N/A			34114484A	RK73B1ETTP103J	600000
88	1029818	#N/A			34114484A	RK73B1ETTP103J	600000
89	1029820	#N/A			34114484A	RK73B1ETTP103J	340000
90	1029837	#N/A			34115849A	RK73B1ETTP473J	30000
91	1029845	#N/A			34115851A	RK73B1ETTP330J	80000
92	1029846	#N/A			34115859A	RK73B1JTTD561J	25000
93	1029849	#N/A			34115860A	RK73B2ATTD472J	5000
94	1029855	#N/A			34115864A	RK73B2BTTD6R8J	35000
95	1029860	#N/A			34115881A	RK73G2BTTD1100F	10000
96	1029878	#N/A			34115885A	RK73H1ETTP1003F	60000
97	1029887	#N/A			34115886A	RK73H1ETTP1001F	50000
98	1029916	#N/A			34115895A	RK73H1JTTDD1002F	90000
99	150089-1	#N/A			34118207A	RK73H1ETTP9092F	10000
100	150089-21	#N/A			34120337A	RK73G1ETTP1302F	10000
101	150089-61	#N/A			34121879A	RK73B1ETTP103J	410000
102	150232-1	#N/A			34122297A	RK73B2BTTD330J	20000
103	150250-1	#N/A			34122299A	RK73H1ETTP56R0F	10000
104	150250-37	#N/A			34122300A	RK73H2ATTD1000F	5000
105	150417-26	#N/A			34122303A	RK73H2ATTD7500F	30000
106	150417-3	#N/A			34122317A	RK73H1ETTP2002F	430000
107	150417-4	#N/A			34122320A	RK73B1ETTP243J	50000
108	150417-5	#N/A			34122321A	RK73B1ETTPD202J	20000
109	150417-8	#N/A			34122322A	RK73H1ETTP2001F	10000
110	151477-1	#N/A			34122325A	RK73H1ETTP3012F	10000
111	151887-1	#N/A			34122327A	RK73H1ETTP3742F	20000
112	152098-1	#N/A			34122328A	RK73H1ETTP4702F	20000
113	152591-65	#N/A			34122331A	RK73H1ETTP5111F	10000
114	152816-11	#N/A			34122339A	RK73H1JTTD1003F	25000
115	152816-2	#N/A			34122343A	RK73H1JTTD4752F	10000
116	152816-3	#N/A			34122441A	RK73H2BTTD1501F	30000
117	152846-11	15000			34123615A	RK73H2ATTD4993F	30000
118	152846-57	#N/A			34123623A	RK73H1ETTP4752F	30000
119	152846-66	#N/A			34124091A	RK73H1JTTDD1500F	180000
120	153073-3	#N/A			34124469A	RK73H1JTTD2002F	5000
121	153073-6	35000			34124470A	RK73H1JTTD1303F	45000
122	153473-35	20000			34127326A	RK73H1ETTP5102F	10000
123	153473-37	30000			34127899A	RK73H1JTTDD1001F	30000
124	153473-41	#N/A			34127913A	RK73H1JTTDD2202F	10000
125	153473-47	#N/A			34128699A	RK73B2ATTD103J	5000
126	153473-49	30000			34128702A	RK73G1ETTPD1002F	400000
127	153473-56	10000			34128703A	RK73G1ETTP1502F	20000
128	153473-70	#N/A			34128707A	RK73G1ETTP8201D	10000
129	153473-80	#N/A			34128711A	RK73H1ETTP2201F	140000
130	153473-81	20000			34128718A	RK73H1ETTP1801F	20000
131	153473-83	#N/A			34129698A	RK73H1ETTP1000F	300000
132	153473-89	40000			34129701A	RN731ETTP2201D25	20000
133	153474-1	20000			34130561A	RK73B2ATTD223J	5000
134	153474-14	#N/A			34130563A	RK73G1ETTP1001F	50000
135	153474-25	#N/A			34130566A	RK73B1ETTP753J	10000
136	153474-29	#N/A			34130568A	RN731ETTP18R0D25	40000
137	153474-41	50000			34130571A	RK73G1ETTP1502F	10000
138	153474-42	#N/A			34130576A	RK73H1ETTP1601F	10000
139	153474-8	#N/A			34130577A	RK73H1ETTP3901F	10000
140	153474-9	#N/A			34130581A	RK73B1ETTP392J	30000
141	153509-1	#N/A			34130584A	RK73B1ETTP303J	10000
142	153602-2	#N/A			34130595A	RK73B1JTTDD222J	40000
143	153629-28	#N/A			34130596A	RK73H1ETTP2703F	10000
144	153758-47	#N/A			34130599A	RK73G1ETTP1002F	80000
145	153758-9	20000			34130803A	RK73H1ETTP2151F	10000
146	153758-92	10000			34132031A	RK73Z2ATTD	15000
147	153760-41	5000			34132344A	RK73B1ETTP472J	40000
148	153760-45	#N/A			34132455A	RK73H1ETTP1000F	120000
149	153760-85	#N/A			34133503A	RK73H1ETTP4702F	20000
150	153760-89	#N/A			34133582A	RK73H2ATTD1001F	30000
151	153904-46	#N/A			34133584A	RK73G1ETTP1001F	150000
152	153905-1	#N/A			34133595A	RK73G1ETTP3001F	30000
153	153905-43	#N/A			34133599A	RK73B2ATTD223J	20000
154	153905-58	#N/A			34133601A	RK73H1ETTP3901F	10000
155	153905-62	#N/A			34133903A	RK73H1ETTP1003F	40000
156	153905-66	10000			34133955A	RN73H1ETTP27R0D50	20000
157	153905-74	#N/A			34133956A	RN73H1ETTP2201D25	30000
158	153905-85	10000			34133957A	RN73H1JTTD33R0F25	130000
159	153905-89	20000			34134504A	RK73B1ETTP152J	10000
160	153905-94	10000			34135276A	RN73H1ETTP2202D25	10000
161	153906-1	#N/A			34135519A	SG73P1JTTD1202F	5000
162	153906-18	40000			34135521A	SG73P1JTTD1502F	5000
163	153906-21	#N/A			34136589A	RN73H2ATTD24R0F50	65000
164	153906-26	#N/A			34137196A	RK73B1ETTP151J	20000
165	153906-30	#N/A			34140496A	RK73G1ETTP2701F	60000
166	153906-33	10000			34141049A	RK73B1ETTP683J	20000
167	153906-37	#N/A			34141472A	RK73H1ETTP8660F	10000
168	153906-4	#N/A			34144211A	RK73H1JTTD1153F	25000
169	153906-46	#N/A			34144214A	RK73H1JTTD2371F	5000
170	153906-52	20000			34144215A	RK73H2ETTD1001F	10000
171	153906-55	10000			34144223A	RK73H1JTTD4992F	5000
172	153906-57	#N/A			34144225A	RK73B2ETTD361J	25000
173	153906-58	#N/A			34144503A	RK73H2ATTD1501F	55000
174	153906-65	#N/A			34145660A	RK73H2ATTD2201F	10000
175	153906-66	#N/A			34148426A	RK73H2ATTD8250F	10000
176	153906-67	#N/A			34149755A	RK73H1ETTP1501F	20000
177	153906-68	10000			34149979A	RK73G2BTTD60R4D	20000
178	153906-69	#N/A			34150868A	RK73H1ETTP5100F	60000
179	153906-71	10000			34156392A	RK73H1JTTD1101D	5000
180	153906-72	20000			34159170A	RK73H2ATTD1502F	5000
181	153906-73	#N/A			34159786A	RK73H2ATTD1001F	5000
182	153906-79	#N/A			34160029A	RK73H1ETTP9310F	20000
183	153906-85	#N/A			34160029A	RK73H1ETTP9310F	100000
184	153906-91	#N/A			34160033A	RK73H1ETTP3651F	480000
185	153907-1	340000			34160037A	RK73H1ETTP8061F	90000
186	153907-18	#N/A			34160038A	RK73H1ETTP3322F	90000
187	153907-27	10000			34160240A	RK73H1ETTP4022F	20000
188	153907-30	50000			34160242A	RK73H1ETTP4532F	20000
189	153907-34	#N/A			34160259A	RK73H1ETTP1213F	130000
190	153907-37	#N/A			34160331A	RK73H1ETTP1004F	10000
191	153907-39	30000			34160425A	RK73H1ETTP9102F	60000
192	153907-46	10000			34161170A	RK73H2ATTD2003F	5000
193	153907-47	10000			34161629A	RK73G1ETTP1201F	10000
194	153907-49	20000			34161848A	RK73H1ETTP3000F	10000
195	153907-51	#N/A			34171236A	RK73B1ETTP470J	80000
196	153907-53	#N/A			34173258A	RK73H1ETTP33R0F	600000
197	153907-56	10000			34173258A	RK73H1ETTP33R0F	370000
198	153907-58	#N/A			34173265A	RK73H1ETTP2400F	450000
199	153907-59	#N/A			34176128A	RK73H1ETTP49R9F	20000
200	153907-63	#N/A			34177748A	RK73B1JTTD750J	80000
201	153907-64	#N/A			34179748A	RK73H2ETTD7500F	10000
202	153907-66	#N/A			34179860A	RK73H1ETTP82R0F	80000
203	153907-73	10000			34181310A	RK73H2BTTD1202F	5000
204	153907-79	10000			34183690A	RK73B1ETTP473J	20000
205	153907-85	#N/A			34184347A	RK73H1ETTP4751F	160000
206	153907-9	#N/A			34186033A	RK73H1ETTP3600F	190000
207	153907-90	#N/A			34188636A	RK73H2ATTD4751F	60000
208	153908-1	70000			34189468A	RK73H1ETTP6652F	60000
209	153908-30	#N/A			34190560A	RK73B1ETTP270J	80000
210	153908-31	#N/A			34191814A	RK73H1ETTP1152F	60000
211	153908-6	#N/A			34194516A	RK73H1ETTP4021F	30000
212	153908-68	20000			34195937A	RK73H2ATTD7502F	30000
213	153908-9	10000			34196125A	RK73B1ETTP202J	10000
214	153937-1	#N/A			34201531A	RK73H2ATTD1003F	15000
215	154005-1	#N/A			34203078A	RN731ETTP3301D25	40000
216	154108-1	10000			34203079A	RN731ETTP1000D25	40000
217	154108-2	#N/A			34203103A	RN731ETTP1802D25	40000
218	154110-1	#N/A			34206175A	RN73H1ETTP1002B25	20000
219	154110-3	60000			34207548A	RK73B2ATTD471J	5000
220	154112-1	400000			34208907A	RK73B1ETTP101J	190000
221	154119-1	#N/A			34208917A	RK73B1ETTP472J	10000
222	154120-13	#N/A			34208975A	RK73H1ETTP2701F	20000
223	154120-22	#N/A			34208976A	RK73H1ETTP2002F	40000
224	154120-24	#N/A			34209026A	RK73B1ETTP102J	20000
225	154120-27	#N/A			34209043A	RK73H1ETTP1001F	80000
226	154383-34	5000			34210649A	RK73H1ETTP1802F	80000
227	154383-47	#N/A			34210669A	RK73H1ETTP3302F	10000
228	154383-54	#N/A			34225998A	RK73G1ETTPD4702F	80000
229	154383-66	5000			34226921A	RK73H1JTTD47R0F	10000
230	154383-73	#N/A			34231303A	RK73H1ETTP3321F	30000
231	154383-76	#N/A			34240132A	RK73H1ETTP6802F	10000
232	154384-26	#N/A			34250426A	RK73H1ETTP3900F	10000
233	154384-38	10000			34253582A	RK73H1ETTP3602F	170000
234	154384-44	#N/A			34253623A	RK73H1ETTP4300F	160000
235	154384-51	#N/A			34255260A	RK73H1ETTP1783F	20000
236	154385-12	#N/A			34259633A	RK73H2ATTD3322F	60000
237	154385-30	#N/A			34267138A	RK73H1ETTPD51R0F	20000
238	154385-66	#N/A			34297465A	RK73H2ATTD2262F	5000
239	154385-97	#N/A			34297549A	RK73H2ATTD5900F	15000
240	154386-17	#N/A			A008C215	SG73P1JTTDD1001F	30000
241	154468-2	#N/A
242	154468-4	5000
243	154486-134	15000
244	154486-162	#N/A
245	154486-177	#N/A
Sheet2

Cell Formulas
Range	Formula
B2:B245	B2	=VLOOKUP(A2,E:G,3,FALSE)

 

[mrshl9898]

Yeah, has to be extra characters in E.

Do D as a new column = TRIM(E2) and use either formula

 

[toony]

Ok what does =LEN(E2) produce?

I just tried =VLOOKUP(LEN(A2),E:G,3,FALSE) as suggested but it brings no results at all only NAs

 

[toony]

I just tried =VLOOKUP(LEN(A2),E:G,3,FALSE) as suggested but it brings no results at all only NAs

Actually, is that what you meant?

 

[steve the fish]

Yeah, has to be extra characters in E.

Do D as a new column = TRIM(E2) and use either formula

I just tried =VLOOKUP(LEN(A2),E:G,3,FALSE) as suggested but it brings no results at all only NAs

Trim wont work as i already gave a potential solution to create textualised number by using trim.

This table im guessing is copied from an internet source and contains hidden characters.

I didnt say put the LEN in the VLOOKUP. I just want to know what =LEN(E2) produces

 

[toony]

Trim wont work as i already gave a potential solution to create textualised number by using trim.

This table im guessing is copied from an internet source and contains hidden characters.

I didnt say put the LEN in the VLOOKUP. I just want to know what =LEN(E2) produces

7 in A2

 

[toony]

7 in A2

B2 i men sorry

 

[steve the fish]

E2. Not A2 or B2.

 

[toony]

Trim wont work as i already gave a potential solution to create textualised number by using trim.

This table im guessing is copied from an internet source and contains hidden characters.

I didnt say put the LEN in the VLOOKUP. I just want to know what =LEN(E2) produces

That's correct. It's macros and the lot and yes, I gather it's because of that but assumed that once copied in a new spreadsheet as plain it would work

 

[jtakw]

Hi,

May be this is worth a try before you go and have to recopy the whole spreadsheet:

=LOOKUP(2,1/SEARCH(" "&A2&" "," "&E$2:E$245&" "),G$2:G$245)

 

[Peter_SSs]

Could your Calculation be set to manual?

Check in File - Options - Formulas and make sure it is set to Automatic