Vlooup workin very randomly

toony

New Member
Joined
Mar 20, 2014
Messages
43
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
ABCDEFG
1Componentpartkoa partTABLE ARRAY
21015073#N/A1015099RK73B2BTTDD330J20000
31015099#N/A1015357RK73Z2BTTD10000
41015357#N/A1023088RK73B2BTTD472J5000
51015776#N/A1024425RK73B1JTTD100J5000
61016099#N/A1024473RK73B1JTTDD102J10000
71016175#N/A1024505RK73B1JTTDD223J10000
81022224#N/A1024517RK73B1JTTD683J5000
91023020#N/A1024529RK73B1JTTD224J5000
101023088#N/A1026484RK73H2ATTD2001F15000
111023253#N/A1026599RK73H2ATTD3162F5000
121023261#N/A1026616RK73H2ATTD4752F5000
131023298#N/A1029590RK73H1JTTD2000F5000
141023380#N/A1029629RK73H1JTTD5110F10000
151023398#N/A1029751RK73H1JTTD9531F5000
161023414#N/A1029849RK73H1JTTDB1003F80000
171023543#N/A152846-11RK73H1JTTD1272F15000
181024401#N/A153073-6RK73H2ATTD8200F35000
191024425#N/A153473-35RK73B1ETTPD270J20000
201024435#N/A153473-37RK73B1ETTP330J30000
211024452#N/A153473-49RK73B1ETTP101J30000
221024473#N/A153473-56RK73B1ETTP201J10000
231024484#N/A153473-81RK73B1ETTP222J20000
241024505#N/A153473-89RK73B1ETTP472J40000
251024508#N/A153474-1RK73B1ETTP103J20000
261024513#N/A153474-41RK73B1ETTP474J50000
271024517#N/A153758-9RK73B1JTTD2R2J20000
281024520#N/A153758-92RK73B1JTTD622J10000
291024529#N/A153760-41RK73B2BTTD470J5000
301024538#N/A153905-66RK73H1ETTP4750F10000
311025913#N/A153905-85RK73H1ETTP7500F10000
321026359#N/A153905-89RK73H1ETTP8250F20000
331026376#N/A153905-94RK73H1ETTP9310F10000
341026443#N/A153906-18RK73H1ETTP1501F40000
351026472#N/A153906-33RK73H1ETTP2151F10000
361026481#N/A153906-52RK73H1ETTP3401F20000
371026484#N/A153906-55RK73H1ETTP3651F10000
381026486#N/A153906-68RK73H1ETTP4991F10000
391026487#N/A153906-71RK73H1ETTP5361F10000
401026497#N/A153906-72RK73H1ETTP5491F20000
411026547#N/A153907-1RK73H1ETTPD1002F340000
421026568#N/A153907-27RK73H1ETTP1872F10000
431026599#N/A153907-30RK73H1ETTP2002F50000
441026616#N/A153907-39RK73H1ETTP2492F30000
451026647#N/A153907-46RK73H1ETTP2942F10000
461029561#N/A153907-47RK73H1ETTP3012F10000
471029590#N/A153907-49RK73H1ETTP3162F20000
481029603#N/A153907-56RK73H1ETTP3742F10000
491029619#N/A153907-73RK73H1ETTP5622F10000
501029620#N/A153907-79RK73H1ETTP6492F10000
511029629#N/A153908-1RK73H1ETTP1003F70000
521029657#N/A153908-68RK73H1ETTP4993F20000
531029677#N/A153908-9RK73H1ETTP1213F10000
541029683#N/A154108-1RK73H1ETTP1004F10000
551029686#N/A154110-3RK73H1JTTD2202F60000
561029689#N/A154112-1RK73Z1ETTPD400000
571029691#N/A154383-34RK73H2BTTD22R1F5000
581029693#N/A154383-66RK73H2BTTD47R5F5000
591029695#N/A154384-38RK73H2BTTD2430F10000
601029696#N/A154468-4SR732BTTDR110F5000
611029698#N/A154486-134RK73H2ATTD24R3F15000
621029700#N/A154486-273RK73H2ATTD6810F25000
631029707#N/A154592-24RK73H1ETTP2700F10000
641029708#N/A154592-25RK73H1ETTP3000F10000
651029714#N/A154592-31RK73H1ETTP5100F10000
661029715#N/A154592-35RK73H1ETTP8200F20000
671029722#N/A154592-41RK73H1ETTP2401F10000
681029725#N/A154592-42RK73H1ETTP2701F10000
691029728#N/A154592-51RK73H1ETTP6201F20000
701029731#N/A154592-55RK73H1ETTP1202F10000
711029736#N/A154592-66RK73H1ETTP4702F50000
721029741#N/A154592-85RK73H1ETTP5103F20000
731029751#N/A154593-3RK73H1JTTD18R0F5000
741029753#N/A154593-40RK73H1JTTD2201F10000
751029757#N/A154593-48RK73H1JTTD4701F20000
761029764#N/A154593-49RK73H1JTTD5101F15000
771029765#N/A154593-56RK73H1JTTD1602F15000
781029766#N/A154593-60RK73H1JTTD2702F15000
791029768#N/A154593-66RK73H1JTTDD4702F10000
801029771#N/A155343-1RK73H2ETTD60R4F5000
811029778#N/A155565-3WK73R2JTTE242J4000
821029782#N/A155915-1RK73Z2ATTDD30000
831029789#N/A156881-1SR732ATTDR10J15000
841029791#N/A200314-1RK73Z1JTTDD190000
851029799#N/A201219-35RK73B2HTTE271J8000
861029804#N/A34114484ARK73B1ETTP103J600000
871029815#N/A34114484ARK73B1ETTP103J600000
881029818#N/A34114484ARK73B1ETTP103J600000
891029820#N/A34114484ARK73B1ETTP103J340000
901029837#N/A34115849ARK73B1ETTP473J30000
911029845#N/A34115851ARK73B1ETTP330J80000
921029846#N/A34115859ARK73B1JTTD561J25000
931029849#N/A34115860ARK73B2ATTD472J5000
941029855#N/A34115864ARK73B2BTTD6R8J35000
951029860#N/A34115881ARK73G2BTTD1100F10000
961029878#N/A34115885ARK73H1ETTP1003F60000
971029887#N/A34115886ARK73H1ETTP1001F50000
981029916#N/A34115895ARK73H1JTTDD1002F90000
99150089-1#N/A34118207ARK73H1ETTP9092F10000
100150089-21#N/A34120337ARK73G1ETTP1302F10000
101150089-61#N/A34121879ARK73B1ETTP103J410000
102150232-1#N/A34122297ARK73B2BTTD330J20000
103150250-1#N/A34122299ARK73H1ETTP56R0F10000
104150250-37#N/A34122300ARK73H2ATTD1000F5000
105150417-26#N/A34122303ARK73H2ATTD7500F30000
106150417-3#N/A34122317ARK73H1ETTP2002F430000
107150417-4#N/A34122320ARK73B1ETTP243J50000
108150417-5#N/A34122321ARK73B1ETTPD202J20000
109150417-8#N/A34122322ARK73H1ETTP2001F10000
110151477-1#N/A34122325ARK73H1ETTP3012F10000
111151887-1#N/A34122327ARK73H1ETTP3742F20000
112152098-1#N/A34122328ARK73H1ETTP4702F20000
113152591-65#N/A34122331ARK73H1ETTP5111F10000
114152816-11#N/A34122339ARK73H1JTTD1003F25000
115152816-2#N/A34122343ARK73H1JTTD4752F10000
116152816-3#N/A34122441ARK73H2BTTD1501F30000
117152846-111500034123615ARK73H2ATTD4993F30000
118152846-57#N/A34123623ARK73H1ETTP4752F30000
119152846-66#N/A34124091ARK73H1JTTDD1500F180000
120153073-3#N/A34124469ARK73H1JTTD2002F5000
121153073-63500034124470ARK73H1JTTD1303F45000
122153473-352000034127326ARK73H1ETTP5102F10000
123153473-373000034127899ARK73H1JTTDD1001F30000
124153473-41#N/A34127913ARK73H1JTTDD2202F10000
125153473-47#N/A34128699ARK73B2ATTD103J5000
126153473-493000034128702ARK73G1ETTPD1002F400000
127153473-561000034128703ARK73G1ETTP1502F20000
128153473-70#N/A34128707ARK73G1ETTP8201D10000
129153473-80#N/A34128711ARK73H1ETTP2201F140000
130153473-812000034128718ARK73H1ETTP1801F20000
131153473-83#N/A34129698ARK73H1ETTP1000F300000
132153473-894000034129701ARN731ETTP2201D2520000
133153474-12000034130561ARK73B2ATTD223J5000
134153474-14#N/A34130563ARK73G1ETTP1001F50000
135153474-25#N/A34130566ARK73B1ETTP753J10000
136153474-29#N/A34130568ARN731ETTP18R0D2540000
137153474-415000034130571ARK73G1ETTP1502F10000
138153474-42#N/A34130576ARK73H1ETTP1601F10000
139153474-8#N/A34130577ARK73H1ETTP3901F10000
140153474-9#N/A34130581ARK73B1ETTP392J30000
141153509-1#N/A34130584ARK73B1ETTP303J10000
142153602-2#N/A34130595ARK73B1JTTDD222J40000
143153629-28#N/A34130596ARK73H1ETTP2703F10000
144153758-47#N/A34130599ARK73G1ETTP1002F80000
145153758-92000034130803ARK73H1ETTP2151F10000
146153758-921000034132031ARK73Z2ATTD15000
147153760-41500034132344ARK73B1ETTP472J40000
148153760-45#N/A34132455ARK73H1ETTP1000F120000
149153760-85#N/A34133503ARK73H1ETTP4702F20000
150153760-89#N/A34133582ARK73H2ATTD1001F30000
151153904-46#N/A34133584ARK73G1ETTP1001F150000
152153905-1#N/A34133595ARK73G1ETTP3001F30000
153153905-43#N/A34133599ARK73B2ATTD223J20000
154153905-58#N/A34133601ARK73H1ETTP3901F10000
155153905-62#N/A34133903ARK73H1ETTP1003F40000
156153905-661000034133955ARN73H1ETTP27R0D5020000
157153905-74#N/A34133956ARN73H1ETTP2201D2530000
158153905-851000034133957ARN73H1JTTD33R0F25130000
159153905-892000034134504ARK73B1ETTP152J10000
160153905-941000034135276ARN73H1ETTP2202D2510000
161153906-1#N/A34135519ASG73P1JTTD1202F5000
162153906-184000034135521ASG73P1JTTD1502F5000
163153906-21#N/A34136589ARN73H2ATTD24R0F5065000
164153906-26#N/A34137196ARK73B1ETTP151J20000
165153906-30#N/A34140496ARK73G1ETTP2701F60000
166153906-331000034141049ARK73B1ETTP683J20000
167153906-37#N/A34141472ARK73H1ETTP8660F10000
168153906-4#N/A34144211ARK73H1JTTD1153F25000
169153906-46#N/A34144214ARK73H1JTTD2371F5000
170153906-522000034144215ARK73H2ETTD1001F10000
171153906-551000034144223ARK73H1JTTD4992F5000
172153906-57#N/A34144225ARK73B2ETTD361J25000
173153906-58#N/A34144503ARK73H2ATTD1501F55000
174153906-65#N/A34145660ARK73H2ATTD2201F10000
175153906-66#N/A34148426ARK73H2ATTD8250F10000
176153906-67#N/A34149755ARK73H1ETTP1501F20000
177153906-681000034149979ARK73G2BTTD60R4D20000
178153906-69#N/A34150868ARK73H1ETTP5100F60000
179153906-711000034156392ARK73H1JTTD1101D5000
180153906-722000034159170ARK73H2ATTD1502F5000
181153906-73#N/A34159786ARK73H2ATTD1001F5000
182153906-79#N/A34160029ARK73H1ETTP9310F20000
183153906-85#N/A34160029ARK73H1ETTP9310F100000
184153906-91#N/A34160033ARK73H1ETTP3651F480000
185153907-134000034160037ARK73H1ETTP8061F90000
186153907-18#N/A34160038ARK73H1ETTP3322F90000
187153907-271000034160240ARK73H1ETTP4022F20000
188153907-305000034160242ARK73H1ETTP4532F20000
189153907-34#N/A34160259ARK73H1ETTP1213F130000
190153907-37#N/A34160331ARK73H1ETTP1004F10000
191153907-393000034160425ARK73H1ETTP9102F60000
192153907-461000034161170ARK73H2ATTD2003F5000
193153907-471000034161629ARK73G1ETTP1201F10000
194153907-492000034161848ARK73H1ETTP3000F10000
195153907-51#N/A34171236ARK73B1ETTP470J80000
196153907-53#N/A34173258ARK73H1ETTP33R0F600000
197153907-561000034173258ARK73H1ETTP33R0F370000
198153907-58#N/A34173265ARK73H1ETTP2400F450000
199153907-59#N/A34176128ARK73H1ETTP49R9F20000
200153907-63#N/A34177748ARK73B1JTTD750J80000
201153907-64#N/A34179748ARK73H2ETTD7500F10000
202153907-66#N/A34179860ARK73H1ETTP82R0F80000
203153907-731000034181310ARK73H2BTTD1202F5000
204153907-791000034183690ARK73B1ETTP473J20000
205153907-85#N/A34184347ARK73H1ETTP4751F160000
206153907-9#N/A34186033ARK73H1ETTP3600F190000
207153907-90#N/A34188636ARK73H2ATTD4751F60000
208153908-17000034189468ARK73H1ETTP6652F60000
209153908-30#N/A34190560ARK73B1ETTP270J80000
210153908-31#N/A34191814ARK73H1ETTP1152F60000
211153908-6#N/A34194516ARK73H1ETTP4021F30000
212153908-682000034195937ARK73H2ATTD7502F30000
213153908-91000034196125ARK73B1ETTP202J10000
214153937-1#N/A34201531ARK73H2ATTD1003F15000
215154005-1#N/A34203078ARN731ETTP3301D2540000
216154108-11000034203079ARN731ETTP1000D2540000
217154108-2#N/A34203103ARN731ETTP1802D2540000
218154110-1#N/A34206175ARN73H1ETTP1002B2520000
219154110-36000034207548ARK73B2ATTD471J5000
220154112-140000034208907ARK73B1ETTP101J190000
221154119-1#N/A34208917ARK73B1ETTP472J10000
222154120-13#N/A34208975ARK73H1ETTP2701F20000
223154120-22#N/A34208976ARK73H1ETTP2002F40000
224154120-24#N/A34209026ARK73B1ETTP102J20000
225154120-27#N/A34209043ARK73H1ETTP1001F80000
226154383-34500034210649ARK73H1ETTP1802F80000
227154383-47#N/A34210669ARK73H1ETTP3302F10000
228154383-54#N/A34225998ARK73G1ETTPD4702F80000
229154383-66500034226921ARK73H1JTTD47R0F10000
230154383-73#N/A34231303ARK73H1ETTP3321F30000
231154383-76#N/A34240132ARK73H1ETTP6802F10000
232154384-26#N/A34250426ARK73H1ETTP3900F10000
233154384-381000034253582ARK73H1ETTP3602F170000
234154384-44#N/A34253623ARK73H1ETTP4300F160000
235154384-51#N/A34255260ARK73H1ETTP1783F20000
236154385-12#N/A34259633ARK73H2ATTD3322F60000
237154385-30#N/A34267138ARK73H1ETTPD51R0F20000
238154385-66#N/A34297465ARK73H2ATTD2262F5000
239154385-97#N/A34297549ARK73H2ATTD5900F15000
240154386-17#N/AA008C215SG73P1JTTDD1001F30000
241154468-2#N/A
242154468-45000
243154486-13415000
244154486-162#N/A
245154486-177#N/A
Sheet2
Cell Formulas
RangeFormula
B2:B245B2=VLOOKUP(A2,E:G,3,FALSE)
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,601
Office Version
  1. 365
Platform
  1. Windows
I dont know its been so difficult to answer the question what does =LEN(E2) produce. But as you say this is a table from an internet source and you are comfortable with macros then run this line changing sheet name to suit your sheet name:

Sheets("Sheet1").Columns("E:G").Replace Chr(160), ""
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,951
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
You trimmed the lookup value not the array..
 

toony

New Member
Joined
Mar 20, 2014
Messages
43
Hi. Sorry for late reply but didin't seen it. The reason for it not to work is because it had several macros and was advise at the time that it having macros the vlookup (and most functions) is not relible at all and my team not using it. Many thanks
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,601
Office Version
  1. 365
Platform
  1. Windows
All built in functions are reliable whether used in VBA or the worksheet. Its always the user making mistakes that cause functions to produce seemingly erroneous results. Whoever told you differently is incorrect.
 

Forum statistics

Threads
1,141,292
Messages
5,705,529
Members
421,399
Latest member
hjweiss00

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top