VLOOKUP using a csv file

A

a13ks

New Member
Joined
Jan 10, 2018
Messages
15
Hello all,

I'm hoping someone can help as I've driven myself insane trying to get to the bottom of it. I have a csv file (attached) that is delivered to me and I am hoping to utilise the info to complete an alternative spreadsheet.

As you can see I am looking to use the 3 letter CODE (eg BDJ in column B) to identify my target and need the TOTAL in Column E.

I thought I was successful using the simple =VLOOKUP(H2,$B$2:$F$300,5,TRUE). Unfortunately though it wasn't the case. Some of the requests return a correct value, but some don't. I think it may be due to there being a variable amount of rows with the same code

I've tried INDEX too but no luck there either. This only returns the value when there is only one code.

Please help!
?


CSV DATA LOOKUP.xlsx
ABCDEFGH
1ACCOUNT NUMBERCODEMATDATENoTOTALCODEVLOOKUP
2D183001BDJBUDL19.01.2022437BDJ443
3D185168BDJBUDL19.01.20222DGP1618
4D185186BDJBUDL19.01.20224443MWH6017
5D157103DGPDAGE19.01.20221255NDG6017
6D183579DGPDAGE19.01.2022325NRC6017
7D185155DGPDAGE19.01.202230NST6017
8D185168DGPDAGE19.01.20224STP2618
9D185186DGPDAGE19.01.202241618TBT2618
10D183001MWHMIDW19.01.20222898WHT8686
11D185153MWHMIDW19.01.202218016WSM21136
12D185168MWHMIDW19.01.20224WST21136
13D185186MWHMIDW19.01.2022420922WVP21136
14D183001NDGN-DE19.01.202253CHI443
15D183588NDGN-DE19.01.2022997CST443
16D185152NDGN-DE19.01.202218624DHT1618
17D185168NDGN-DE19.01.20224DMB10857
18D185170NDGN-DE19.01.20222420ELA4421
19D185186NDGN-DE19.01.2022422102ELS5147
20D157103NRCNEWH19.01.2022100FHT1300
21D183579NRCNEWH19.01.2022785HAH3987
22D185155NRCNEWH19.01.202220HEA19617
23D185164NRCNEWH19.01.20224320HGZ1747
24D185168NRCNEWH19.01.20222HHB426
25D185186NRCNEWH19.01.20224HHW2201
26D185198NRCNEWH19.01.202255236IGZ4187
27D157031NSTN-SO19.01.2022442IRC5263
28D185168NSTN-SO19.01.20224KBT9704
29D185170NSTN-SO19.01.202232017NNN6017
30D185186NSTN-SO19.01.2022432467RYC7102
31D157220STPST-N19.01.2022351SFR7102
32D157228STPST-N19.01.202223BAB#N/A
33D157231STPST-N19.01.20221OTH269
34D185168STPST-N19.01.20221RRC4873
35D185169STPST-N19.01.202212910RRM7102
36D185186STPST-N19.01.2022413290SMH2618
37D157214TBTTHET19.01.20228WAM8563
38D157231TBTTHET19.01.2022345WBS8686
39D185168TBTTHET19.01.20221WNA21136
40D185169TBTTHET19.01.202279288282YMM7272
41D157101WHTWEL-19.01.2022500
42D157132WHTWEL-19.01.20222576
43D185158WHTWEL-19.01.202257
44D185168WHTWEL-19.01.202215
45D185186WHTWEL-19.01.20227
46D185198WHTWEL-19.01.202233158
47D157231WSMW-SU19.01.20225
48D185168WSMW-SU19.01.20222
49D185169WSMW-SU19.01.202211157
50D185186WSMW-SU19.01.2022111165
51D157214WSTWATT19.01.202264
52D157231WSTWATT19.01.202256
53D185168WSTWATT19.01.20221
54D185169WSTWATT19.01.202246494770
55D151060WVPWOOD19.01.202223552355
56D157101CHICOME20.01.202211
57D157132CHICOME20.01.2022397
58D157228CHICOME20.01.202220
59D185168CHICOME20.01.20227
60D185169CHICOME20.01.202238445
61D185186CHICOME20.01.2022438884
62D157132CSTCOME20.01.2022702
63D185168CSTCOME20.01.202213
64D185169CSTCOME20.01.202244870
65D185186CSTCOME20.01.2022445589
66D157214DHTDERE20.01.20221353
67D157220DHTDERE20.01.20227
68D157231DHTDERE20.01.2022179
69D185168DHTDERE20.01.202221541
70D157132DMBDUNM20.01.2022199
71D185168DMBDUNM20.01.202216
72D185169DMBDUNM20.01.202210637
73D185186DMBDUNM20.01.20224
74D185198DMBDUNM20.01.2022110857
75D183576ELAE-LO20.01.20229
76D183579ELAE-LO20.01.2022350
77D185155ELAE-LO20.01.202220
78D185159ELAE-LO20.01.20223070
79D185164ELAE-LO20.01.2022965
80D185168ELAE-LO20.01.20223
81D185186ELAE-LO20.01.202244421
82D157220ELSELY-20.01.202288
83D157231ELSELY-20.01.20222296
84D185168ELSELY-20.01.202212
85D185169ELSELY-20.01.20222733
86D185186ELSELY-20.01.20224
87D185198ELSELY-20.01.2022145147
88D157214FHTFAKE20.01.2022309
89D157220FHTFAKE20.01.202251
90D157231FHTFAKE20.01.2022938
91D185168FHTFAKE20.01.202221300
92D183574HAHHAM-20.01.20227
93D183576HAHHAM-20.01.20221560
94D185155HAHHAM-20.01.202235
95D185164HAHHAM-20.01.20222349
96D185168HAHHAM-20.01.20222
97D185186HAHHAM-20.01.20224
98D185198HAHHAM-20.01.2022303987
99D157101HEAHERT20.01.20221415
100D157132HEAHERT20.01.202272
101D157228HEAHERT20.01.20225
102D185168HEAHERT20.01.202225
103D185169HEAHERT20.01.202218096
104D185186HEAHERT20.01.2022419617
105D183576HGZHACK20.01.2022372
106D183579HGZHACK20.01.2022645
107D185155HGZHACK20.01.202220
108D185159HGZHACK20.01.202210
109D185164HGZHACK20.01.2022693
110D185168HGZHACK20.01.20223
111D185186HGZHACK20.01.202241747
112D183576HHBHAM-20.01.2022401
113D185155HHBHAM-20.01.202210
114D185164HHBHAM-20.01.20227
115D185168HHBHAM-20.01.20222
116D185186HHBHAM-20.01.20224
117D185198HHBHAM-20.01.20222426
118D183574HHWHAM-20.01.202249
119D183576HHWHAM-20.01.202262
120D185155HHWHAM-20.01.202210
121D185164HHWHAM-20.01.20222073
122D185168HHWHAM-20.01.20223
123D185186HHWHAM-20.01.202242201
124D183576IGZISLI20.01.2022938
125D183579IGZISLI20.01.202245
126D185155IGZISLI20.01.202215
127D185164IGZISLI20.01.20223183
128D185168IGZISLI20.01.20222
129D185186IGZISLI20.01.202244187
130D157101IRCILFO20.01.202290
131D157103IRCILFO20.01.2022200
132D183576IRCILFO20.01.20224
133D183579IRCILFO20.01.2022995
134D185155IRCILFO20.01.202240
135D185164IRCILFO20.01.20223920
136D185168IRCILFO20.01.20223
137D185186IRCILFO20.01.20226
138D185198IRCILFO20.01.202255263
139D183574KBTKILB20.01.2022275
140D183576KBTKILB20.01.2022156
141D185155KBTKILB20.01.202230
142D185160KBTKILB20.01.20224730
143D185164KBTKILB20.01.20224507
144D185168KBTKILB20.01.20222
145D185186KBTKILB20.01.202249704
146D157214NNNNORT20.01.20223280
147D157220NNNNORT20.01.202241
148D157231NNNNORT20.01.2022112
149D185168NNNNORT20.01.20228
150D185198NNNNORT20.01.202253446
151D157132RYCROYS20.01.2022495
152D157231RYCROYS20.01.202277
153D185168RYCROYS20.01.20228
154D185169RYCROYS20.01.20229824
155D185186RYCROYS20.01.2022410408
156D157132SFRSAFF20.01.2022129
157D157231SFRSAFF20.01.2022257
158D185168SFRSAFF20.01.202210
159D185169SFRSAFF20.01.202210700
160D185186SFRSAFF20.01.20224
161D185198SFRSAFF20.01.2022111101
162D183001TBWTORB20.01.202219098
163D185168TBWTORB20.01.20222
164D185186TBWTORB20.01.20224
165D185198TBWTORB20.01.20221519119
166D157031WMEWEST20.01.2022410
167D183587WMEWEST20.01.20225245
168D185168WMEWEST20.01.202212
169D185186WMEWEST20.01.20227
170D185198WMEWEST20.01.20222735947
171D185168YMAYRMO20.01.20221
172D185169YMAYRMO20.01.20222034020341
173D157213BABBECC21.01.2022285
174D157214BABBECC21.01.20222468
175D185168BABBECC21.01.20223
176D185198BABBECC21.01.2022252781
177D157103BRRBREN21.01.202275
178D185155BRRBREN21.01.202215
179D185164BRRBREN21.01.20221810
180D185186BRRBREN21.01.202241904
181D157220CBTCAMB21.01.2022689
182D157231CBTCAMB21.01.202256
183D185168CBTCAMB21.01.202214
184D185169CBTCAMB21.01.20228426
185D185186CBTCAMB21.01.202249189
186D157213DMEDISS21.01.202259
187D157214DMEDISS21.01.202233
188D157231DMEDISS21.01.20222
189D185168DMEDISS21.01.20221
190D185169DMEDISS21.01.202285018596
191D185168ESEEAST21.01.20223
192D185169ESEEAST21.01.20225532
193D185186ESEEAST21.01.202215536
194D157213LSJLOWE21.01.2022365
195D157214LSJLOWE21.01.20225598
196D157231LSJLOWE21.01.20228
197D185168LSJLOWE21.01.202212
198D185198LSJLOWE21.01.2022346017
199D183001OTHOTTE21.01.2022261
200D185168OTHOTTE21.01.20221
201D185186OTHOTTE21.01.20227269
202D157103RRCROMF21.01.20224400
203D183579RRCROMF21.01.202260
204D185155RRCROMF21.01.202230
205D185164RRCROMF21.01.2022352
206D185168RRCROMF21.01.20221
207D185198RRCROMF21.01.2022304873
208D185169RRMRRC-21.01.20227095
209D185186RRMRRC-21.01.202277102
210D183001SMHSIDM21.01.20222574
211D185168SMHSIDM21.01.202237
212D185186SMHSIDM21.01.202272618
213D157214WAMWYM-21.01.2022119
214D185168WAMWYM-21.01.20221
215D185169WAMWYM-21.01.202284438563
216D157220WBSWISB21.01.2022706
217D157231WBSWISB21.01.202227
218D185168WBSWISB21.01.202215
219D185169WBSWISB21.01.20227928
220D185186WBSWISB21.01.20224
221D185198WBSWISB21.01.202268686
222D185168WNAWAVE21.01.20221
223D185169WNAWAVE21.01.20222113521136
224D157213YMMYARM21.01.202227
225D157214YMMYARM21.01.20227201
226D157220YMMYARM21.01.202217
227D157231YMMYARM21.01.20227
228D185168YMMYARM21.01.20228
229D185198YMMYARM21.01.2022127272
Sheet2
Cell Formulas
RangeFormula
H2:H40H2=VLOOKUP(G2,$B$2:$F$283,5,TRUE)
 
Sort by date Sort by votes
Try . . .

G2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=INDEX($F$2:$F$283,MATCH(1,IF($B$2:$B$283=G2,IF(LEN($F$2:$F$283)>0,1)),0))

However, if you're using Dynamic Excel, you can use the following formula instead . . .

G2, confirmed with just ENTER, and copied down:

=INDEX(FILTER(B$2:$F$283,(B$2:$B$283=G2)*(LEN(F$2:$F$283)>0)),5)

By the way, you should update your profile and specify which version of Excel you're using so that in future a solution can be offered accordingly.

Hope this helps!
 
Upvote 0
You could probably just use SUMIF. In H2, put

Excel Formula: 
=SUMIF($B$2:$B$283,G2,$E$2:$E$283)

and drag down.
 
Upvote 0
Solution
Eric W said:
=SUMIF($B$2:$B$283,G2,$E$2:$E$283)
Click to expand...
Well, I suppose taking the easy way makes sense.

Works a treat. Thank you. Can't believe I missed it.

Which method would you choose to try and keep an already heavily loaded sheet performing well?
 
Upvote 0
Eric W said:
You could probably just use SUMIF. In H2, put

Excel Formula: 
=SUMIF($B$2:$B$283,G2,$E$2:$E$283)

and drag down.
Click to expand...
Also, if I changed it to calculate column F instead of E would it not perform better as it only has the total value which is what I require?
 
Upvote 0
You could change it to column F instead of E and it should work the same. I doubt you'd see much performance improvement (if at all) since it'll still have to look at each individual row. The xxIF functions are all pretty efficient though, I don't think it would add a lot of overhead even to a large workbook.

Let us know if you do see any issues though, and we'll take another look. Glad we could help!
 
Upvote 0
Eric W said:
You could change it to column F instead of E and it should work the same. I doubt you'd see much performance improvement (if at all) since it'll still have to look at each individual row. The xxIF functions are all pretty efficient though, I don't think it would add a lot of overhead even to a large workbook.

Let us know if you do see any issues though, and we'll take another look. Glad we could help!
Click to expand...
Thank you so much again. Really appreciate it.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Vlookup using array of column numbers
Replies
5
Views
82
kvsrinivasamurthy
K
J
  • Question Question
Merge several csv files into one workbook
Replies
6
Views
208
alansidman
alansidman
C
  • Question Question
Transpose vertical CSV data set into a linked horizontal table with index match and/or lookup and multiple criteria
Replies
1
Views
98
Fluff
Fluff
D
  • Question Question
Pulling data from csv and place on worksheet without using clipboard?
Replies
1
Views
115
alansidman
alansidman
T
  • Solved
CSV import overwriting the next column after the last one in the CSV file
Replies
7
Views
447
Joe4
Joe4

Share this page

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
Back
Top