Lets say that I have a model number in cell D4 of the “BOM” worksheet that I will search against using a VLOOKUP formula in cell G4.
The value is searched on using the “LOOKUP” worksheet, and once found, it maps back to G4 the associated value in column #2 which is numerical.
Example: =VLOOKUP(D4,LookUp!A1:B485,2,FALSE). G4 is populated with “4” which represents the amount of this model number I have in inventory.
But lets say that I want to do the a VLOOKUP of models in multiple (D4, E4, F4) that return values of 4, 10 and 3, but I want 17 to be displayed in G4. (all three hits are summed together). Is that possible to do?
The value is searched on using the “LOOKUP” worksheet, and once found, it maps back to G4 the associated value in column #2 which is numerical.
Example: =VLOOKUP(D4,LookUp!A1:B485,2,FALSE). G4 is populated with “4” which represents the amount of this model number I have in inventory.
But lets say that I want to do the a VLOOKUP of models in multiple (D4, E4, F4) that return values of 4, 10 and 3, but I want 17 to be displayed in G4. (all three hits are summed together). Is that possible to do?
IB_CONTR_Asset_Listing_Account_Number_Report_MS_L1 (2).xls | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | Model/Make | ACC_DESC | Part#/EDP | ||||||
2 | 1000Z-HEW | [XXXX]HP NO 41 GENUINE TRICOL DJ 820 850 855 870 | 51641A-DS | 1000Z-HEW | |||||
3 | 1000Z-HEW | [XXXX]REMAN INK CART HP 41 51641A TRI-COLOR | 51641A-RM-DS | 1000Z-HEW | |||||
4 | 1000Z-HEW | HP 45 BLACK INK CART 930 PAGE YIELD | 51645A-DS | 1000Z-HEW | 6122-HEW | 990CXI-HEW | 17 | ||
BOM |
IB_CONTR_Asset_Listing_Account_Number_Report_MS_L1 (2).xls | ||||
---|---|---|---|---|
A | B | |||
1 | CFXL4000 | 1 | ||
2 | D320 | 1 | ||
3 | IP3600 | 1 | ||
4 | IRC5540I-CAN | 1 | ||
5 | L100 | 2 | ||
6 | L190-CAN | 1 | ||
7 | L80 | 6 | ||
8 | L90 | 7 | ||
9 | MP830-CAN | 1 | ||
10 | MX340 | 3 | ||
11 | PC920 | 1 | ||
12 | TR8520-CAN | 1 | ||
13 | C1660W-DEL | 1 | ||
14 | 4630-EPS | 1 | ||
15 | WF3620-EPS | 2 | ||
16 | WF3640-EPS | 1 | ||
17 | 1000Z-HEW | 4 | ||
18 | 1012-HEW | 10 | ||
19 | 1020Z-HEW | 1 | ||
20 | 1040-HEW | 1 | ||
21 | 1100Z-HEW | 1 | ||
22 | 1150-HEW | 1 | ||
23 | 1160-HEW | 2 | ||
24 | 1200-HEW | 24 | ||
25 | 1300-HEW | 8 | ||
26 | 1320-HEW | 32 | ||
27 | 1512-HEW | 1 | ||
28 | 1600-HEW | 4 | ||
29 | 2015D-HEW | 16 | ||
30 | 2100-HEW | 1 | ||
31 | 2200DTN-HEW | 2 | ||
32 | 2280-HEW | 1 | ||
33 | 2300-HEW | 6 | ||
34 | 2420-HEW | 3 | ||
35 | 2430-HEW | 8 | ||
36 | 2430N-HEW | 1 | ||
37 | 2512-HEW | 1 | ||
38 | 251DW-HEW | 2 | ||
39 | 2600N-HEW | 3 | ||
40 | 2605-HEW | 1 | ||
41 | 276DW-HEW | 1 | ||
42 | 2840-HEW | 3 | ||
43 | 3050-HEW | 1 | ||
44 | 3055MFP-HEW | 2 | ||
45 | 3520-HEW | 1 | ||
46 | 3800-HEW | 24 | ||
47 | 3830-HEW | 1 | ||
48 | 4000-HEW | 18 | ||
49 | 4050-HEW | 45 | ||
50 | 4050TN-HEW | 1 | ||
51 | 4200-HEW | 51 | ||
52 | 4250N-HEW | 158 | ||
53 | 4300-HEW | 7 | ||
54 | 4350-HEW | 6 | ||
55 | 4500IJ-HEW | 3 | ||
56 | 4520-HEW | 1 | ||
57 | 4600DNM-HEW | 5 | ||
58 | 4650-HEW | 4 | ||
59 | 4700DTN-HEW | 1 | ||
60 | 4700N-HEW | 8 | ||
61 | 4PLUS-HEW | 1 | ||
62 | 5440-HEW | 1 | ||
63 | 5500-HEW | 1 | ||
64 | 5550DN-HEW | 7 | ||
65 | 5650-HEW | 1 | ||
66 | 5-HEW | 3 | ||
67 | 5N-HEW | 1 | ||
68 | 6100-HEW | 6 | ||
69 | 6230-HEW | 17 | ||
70 | 6305-HEW | 1 | ||
71 | 6310-HEW | 2 | ||
72 | 6940-HEW | 6 | ||
73 | 6968-HEW | 1 | ||
74 | 6978-HEW | 1 | ||
75 | 6980-HEW | 3 | ||
76 | 7155-HEW | 1 | ||
77 | 7210-HEW | 1 | ||
78 | 8000-HEW | 4 | ||
79 | 8000N-HEW | 2 | ||
80 | 8000Z-HEW | 1 | ||
81 | 8025-HEW | 1 | ||
82 | 8100E-HEW | 16 | ||
83 | 8100-HEW | 2 | ||
84 | 8150-HEW | 6 | ||
85 | 8210-HEW | 125 | ||
86 | 8500C-HEW | 2 | ||
87 | 8600-HEW | 10 | ||
88 | 8610-HEW | 2 | ||
89 | 8620-HEW | 1 | ||
90 | 8700-HEW | 1 | ||
91 | 8710-HEW | 6 | ||
92 | 8720-HEW | 2 | ||
93 | 8730-HEW | 3 | ||
94 | 8740-HEW | 1 | ||
95 | 9010-HEW | 2 | ||
96 | 9020-HEW | 4 | ||
97 | 9050N-HEW | 4 | ||
98 | 940C-HEW | 1 | ||
99 | 950XI-HEW | 1 | ||
100 | 9800-HEW | 1 | ||
101 | 990CXI-HEW | 3 | ||
102 | C4680-HEW | 1 | ||
103 | CM1415FN-HEW | 5 | ||
104 | CM2320FXI-HEW | 9 | ||
105 | CM4540F-HEW | 1 | ||
106 | CM4540-HEW | 3 | ||
107 | CM4730-HEW | 2 | ||
108 | CM6040-HEW | 1 | ||
109 | CP1025NW-HEW | 2 | ||
110 | CP1215-HEW | 9 | ||
111 | CP1525NW-HEW | 6 | ||
112 | CP2025-HEW | 69 | ||
113 | CP3525DN-HEW | 11 | ||
114 | CP3525X-HEW | 1 | ||
115 | CP4025N-HEW | 2 | ||
116 | CP4525N-HEW | 3 | ||
117 | CP6015X-HEW | 1 | ||
118 | D110A-HEW | 1 | ||
119 | D2460-HEW | 1 | ||
120 | DJ6940-HEW | 7 | ||
121 | DX2000-HEW | 1 | ||
122 | HP2050-HEW | 1 | ||
123 | HP4005-HEW | 1 | ||
124 | HP4240-HEW | 1 | ||
125 | J4580-HEW | 1 | ||
126 | K5400-HEW | 1 | ||
127 | K5400TN-HEW | 1 | ||
128 | K550DTN-HEW | 3 | ||
129 | L7580-HEW | 1 | ||
130 | LJ3052-HEW | 1 | ||
131 | M102W-HEW | 9 | ||
132 | M1212NF-HEW | 6 | ||
133 | M127FN | 1 | ||
134 | M130FW-HEW | 1 | ||
135 | M1522NF-HEW | 4 | ||
136 | M201DW-HEW | 4 | ||
137 | M225DN-HEW | 1 | ||
138 | M251NW-HEW | 3 | ||
139 | M252DW-HEW | 3 | ||
140 | M254DW-HEW | 7 | ||
141 | M2727NF-HEW | 4 | ||
142 | M276NW-HEW | 5 | ||
143 | M277DW-HEW | 7 | ||
144 | M281FDW-HEW | 3 | ||
145 | M3035-HEW | 2 | ||
146 | M375NW-HEW | 1 | ||
147 | M401DNE-HEW | 128 | ||
148 | M402DNE-HEW | 84 | ||
149 | M402N-HEW | 8 | ||
150 | M404DN-HEW | 10 | ||
151 | M425DN-HEW | 11 | ||
152 | M426FDN-HEW | 16 | ||
153 | M428FDN-HEW | 2 | ||
154 | M4345-HEW | 4 | ||
155 | M451NW-HEW | 61 | ||
156 | M452DN-HEW | 42 | ||
157 | M454DN-HEW | 11 | ||
158 | M4555H-HEW | 4 | ||
159 | M475DN-HEW | 6 | ||
160 | M476DN-HEW | 25 | ||
161 | M477FDN-HEW | 7 | ||
162 | M477FDW-HEW | 1 | ||
163 | M477FNW-HEW | 180 | ||
164 | M479FDW-HEW | 19 | ||
165 | M501DN-HEW | 46 | ||
166 | M5035-HEW | 1 | ||
167 | M506DN-HEW | 438 | ||
168 | M507N-HEW | 50 | ||
169 | M521DN-HEW | 2 | ||
170 | M525F-HEW | 12 | ||
171 | M527F-HEW | 34 | ||
172 | M528F-HEW | 2 | ||
173 | M551DN-HEW | 25 | ||
174 | M551N-HEW | 1 | ||
175 | M553N-HEW | 448 | ||
176 | M570DN-HEW | 3 | ||
177 | M575C-HEW | 17 | ||
178 | M577DN-HEW | 1 | ||
179 | M577F-HEW | 28 | ||
180 | M602DN-HEW | 103 | ||
181 | M603DN-HEW | 30 | ||
182 | M603N-HEW | 1 | ||
183 | M604N-HEW | 13 | ||
184 | M605DN-HEW | 189 | ||
185 | M606DN-HEW | 26 | ||
186 | M607DN-HEW | 31 | ||
187 | M607N-HEW | 1 | ||
188 | M608DN-HEW | 74 | ||
189 | M630F-HEW | 5 | ||
190 | M651N-HEW | 5 | ||
191 | M653DN-HEW | 2 | ||
192 | M681Z-HEW | 1 | ||
193 | M806DN-HEW | 4 | ||
194 | OJ7612-HEW | 1 | ||
195 | P1005-HEW | 3 | ||
196 | P1006-HEW | 2 | ||
197 | P1102W-HEW | 20 | ||
198 | P1505-HEW | 42 | ||
199 | P1606DN-HEW | 18 | ||
200 | P2035-HEW | 115 | ||
201 | P2055D-HEW | 114 | ||
202 | P3005D-HEW | 34 | ||
203 | P3010-HEW | 6 | ||
204 | P3015-HEW | 217 | ||
205 | P4014-HEW | 26 | ||
206 | P4515N-HEW | 82 | ||
207 | X476DN-HEW | 6 | ||
208 | X576DW-HEW | 1 | ||
209 | X585DN-HEW | 3 | ||
210 | MS315DN-IBM | 57 | ||
211 | MS825DN-IBM | 42 | ||
212 | XM5170-IBM | 77 | ||
213 | 1352-IBM | 2 | ||
214 | C543DN-IBM | 1 | ||
215 | C734N-IBM | 2 | ||
216 | C746DN-IBM | 10 | ||
217 | CS310N-IBM | 1 | ||
218 | CS410DN-IBM | 4 | ||
219 | CS510DE-IBM | 2 | ||
220 | CX410E-IBM | 2 | ||
221 | E260D-IBM | 1 | ||
222 | E260DN-IBM | 10 | ||
223 | E340-IBM | 4 | ||
224 | E350D-IBM | 2 | ||
225 | E352DN-IBM | 1 | ||
226 | E360DN-IBM | 24 | ||
227 | E460DN-IBM | 1 | ||
228 | MS310DN-IBM | 12 | ||
229 | MS312DN-IBM | 8 | ||
230 | MS321DN-IBM | 2 | ||
231 | MS410DN-IBM | 3 | ||
232 | MS415DN-IBM | 7 | ||
233 | MS510DN-IBM | 3 | ||
234 | MS610DN-IBM | 8 | ||
235 | MS710DN-IBM | 1 | ||
236 | MS810DTN-IBM | 51 | ||
237 | MS810N-IBM | 1 | ||
238 | MS811DN-IBM | 88 | ||
239 | MS812DN-IBM | 166 | ||
240 | MS823DN-IBM | 1 | ||
241 | MX310DN-IBM | 1 | ||
242 | MX511DE-IBM | 7 | ||
243 | MX710DHE-IBM | 39 | ||
244 | S1250-IBM | 2 | ||
245 | S815-IBM | 1 | ||
246 | T620Y-IBM | 1 | ||
247 | T632-IBM | 1 | ||
248 | T634-IBM | 3 | ||
249 | T640-IBM | 6 | ||
250 | T644-IBM | 32 | ||
251 | T650N-IBM | 6 | ||
252 | T652N-IBM | 1 | ||
253 | T654DT-IBM | 248 | ||
254 | T654DTN-IBM | 5 | ||
255 | X204N-IBM | 1 | ||
256 | X364DN-IBM | 1 | ||
257 | X466DE-IBM | 27 | ||
258 | X544DTN-IBM | 2 | ||
259 | X656DE-IBM | 40 | ||
260 | XC4150-IBM | 2 | ||
261 | 1022-HEW | 21 | ||
262 | 1125-DEL | 2 | ||
263 | 1270E-BRT | 3 | ||
264 | 1720-DEL | 1 | ||
265 | 1860C-BRT | 2 | ||
266 | 2165W-SSG | 1 | ||
267 | 2350DN-DEL | 1 | ||
268 | 2820-BRT | 38 | ||
269 | 2840-BRT | 30 | ||
270 | 2920-BRT | 9 | ||
271 | 2940-BRT | 5 | ||
272 | 3055MFP-HEW | 2 | ||
273 | 3110CN-DEL | 1 | ||
274 | 3300MFP-XER | 3 | ||
275 | 3550-HEW | 1 | ||
276 | 4100-BRT | 112 | ||
277 | 4100E-BRT | 1 | ||
278 | 4100-HEW | 40 | ||
279 | 450-DYM | 1 | ||
280 | 4750-BRT | 1 | ||
281 | 4750E-BRT | 84 | ||
282 | 5100TN-HEW | 2 | ||
283 | 5750E-BRT | 3 | ||
284 | 575-BRT | 1 | ||
285 | 6122-HEW | 10 | ||
286 | 6988-HEW | 4 | ||
287 | B2360DN-DEL | 1 | ||
288 | B410D-OKI | 1 | ||
289 | B4350-OKI | 3 | ||
290 | B4600-OKI | 3 | ||
291 | B6300-OKI | 3 | ||
292 | B6500-OKI | 10 | ||
293 | B710DN-OKI | 10 | ||
294 | B720DN-OKI | 7 | ||
295 | B721DN-OKI | 13 | ||
296 | C1760NW-DEL | 3 | ||
297 | C530DN-OKI | 1 | ||
298 | C6150DN-OKI | 1 | ||
299 | C711DN-OKI | 2 | ||
300 | CLP325W-SSG | 1 | ||
301 | CLX3170FN-SSG | 1 | ||
302 | CM1312NFI-HEW | 8 | ||
303 | CM3530FS-HEW | 1 | ||
304 | CP1518NI-HEW | 5 | ||
305 | CP3505-HEW | 12 | ||
306 | DCP7020-BRT | 1 | ||
307 | DCP7030-BRT | 2 | ||
308 | DCP7040-BRT | 3 | ||
309 | DCP7065DN-BRT | 4 | ||
310 | GK420T-ZEB | 4 | ||
311 | GX420D-ZEB | 8 | ||
312 | HL2040-BRT | 1 | ||
313 | HL2070N-BRT | 1 | ||
314 | HL2140-BRT | 20 | ||
315 | HL-2170W-BRT | 3 | ||
316 | HL2220-BRT | 2 | ||
317 | HL2230-BRT | 1 | ||
318 | HL-2240D-BRT | 30 | ||
319 | HL2270DW-BRT | 3 | ||
320 | HL3040CN-BRT | 2 | ||
321 | HL3140CW-BRT | 3 | ||
322 | HL5070N-BRT | 1 | ||
323 | HL5150D-BRT | 1 | ||
324 | HL5250DN-BRT | 3 | ||
325 | HL5340D-BRT | 5 | ||
326 | HL5450DN-BRT | 3 | ||
327 | HLL2300D-BRT | 2 | ||
328 | HLL2320D-BRT | 3 | ||
329 | HLL5000D-BRT | 1 | ||
330 | HLL6200DW-BRT | 1 | ||
331 | HP COLLJ 3600N | 5 | ||
332 | HPCOLSJET3500 | 1 | ||
333 | HPLJETM1536DNF | 13 | ||
334 | HPLSJET2820 | 1 | ||
335 | L2340DW-BRT | 2 | ||
336 | L2360DW-BRT | 10 | ||
337 | L2370DW-BRT | 2 | ||
338 | L2685DW-BRT | 1 | ||
339 | LEXMARK C935 | 1 | ||
340 | LJET 600 M601 | 23 | ||
341 | LP2824-ZEB | 1 | ||
342 | M2020W-SSG | 2 | ||
343 | MC3326ADWE-IBM | 1 | ||
344 | MFC4800-BRT | 1 | ||
345 | MFC5490CN-BRT | 1 | ||
346 | MFC6490CW-BRT | 1 | ||
347 | MFC7220-BRT | 4 | ||
348 | MFC7240-BRT | 3 | ||
349 | MFC7365DN-BRT | 1 | ||
350 | MFC7420-BRT | 1 | ||
351 | MFC7440N-BRT | 2 | ||
352 | MFC7820N-BRT | 1 | ||
353 | MFC7840W-BRT | 4 | ||
354 | MFC7860DW-BRT | 2 | ||
355 | MFC8120-BRT | 43 | ||
356 | MFC-8220-BRT | 30 | ||
357 | MFC8460N-BRT | 3 | ||
358 | MFC8710DW-BRT | 1 | ||
359 | MFC8890DW-BRT | 1 | ||
360 | MFC8950DW-BRT | 3 | ||
361 | MFC9130CW-BRT | 1 | ||
362 | MFC9320CW-BRT | 1 | ||
363 | MFC9340CDW-BRT | 1 | ||
364 | MFC9700-BRT | 2 | ||
365 | MFCJ825DW-BRT | 2 | ||
366 | ML-2250-SSG | 1 | ||
367 | ML-2950ND-SSG | 1 | ||
368 | ML-2955DW-SSG | 1 | ||
369 | ML3750ND-SSG | 5 | ||
370 | ML4020ND-SSG | 1 | ||
371 | MS725DVN-IBM | 1 | ||
372 | P4015-HEW | 128 | ||
373 | QLN420-ZEB | 10 | ||
374 | S4M-ZEB | 1 | ||
375 | SCX4623F-SSG | 3 | ||
376 | SCX5639FR-SSG | 1 | ||
377 | SP8300DN-MICR | 1 | ||
378 | TLP2824PLUS-ZEB | 1 | ||
379 | UF6200-PAN | 3 | ||
380 | UF770-PAN | 4 | ||
381 | WF3520-EPS | 1 | ||
382 | ZD410 | 3 | ||
383 | ZD41H22-ZEB | 18 | ||
384 | ZD420T-ZEB | 1 | ||
385 | ZT230-ZEB | 4 | ||
386 | DX800-PAN | 1 | ||
387 | KXFHD351-PAN | 2 | ||
388 | KXFL511-PAN | 2 | ||
389 | UF490-PAN | 1 | ||
390 | UF790-PAN | 1 | ||
391 | UF7950-PAN | 1 | ||
392 | UF-8000-PAN | 1 | ||
393 | UF8200-PAN | 3 | ||
394 | UF895-PAN | 1 | ||
395 | CL7200D | 2 | ||
396 | IM350F | 863 | ||
397 | IM430FTL | 1 | ||
398 | IM550F | 38 | ||
399 | IMC2000 | 307 | ||
400 | IMC3000 | 642 | ||
401 | IMC300F | 80 | ||
402 | IMC4500 | 116 | ||
403 | IMC6000 | 16 | ||
404 | MC250FWB | 3 | ||
405 | MP171SPF | 1 | ||
406 | MP201SPF | 1 | ||
407 | MP301SPF | 43 | ||
408 | MP4001SP | 1 | ||
409 | MP4002SP | 2 | ||
410 | MP4054SP | 1 | ||
411 | MP501SPF | 1 | ||
412 | MP6503SP | 5 | ||
413 | MPC3003 | 21 | ||
414 | MPC3004 | 1 | ||
415 | MPC305SPF | 2 | ||
416 | MPC306SPF | 7 | ||
417 | MPC307 | 59 | ||
418 | MPC4503 | 5 | ||
419 | MPC5503 | 1 | ||
420 | NA | 1 | ||
421 | P501 | 23 | ||
422 | P501TL | 37 | ||
423 | P800 | 14 | ||
424 | P801 | 3 | ||
425 | PC301W | 14 | ||
426 | PC600 | 24 | ||
427 | SP330DN | 1 | ||
428 | SP330SFN | 1 | ||
429 | SP3500SF | 1 | ||
430 | SP3710DN | 7 | ||
431 | SP3710SF | 14 | ||
432 | SP4100N | 1 | ||
433 | SP4100NL | 1 | ||
434 | SP4310N-RIC | 2 | ||
435 | SP8400DN | 1 | ||
436 | SPC342DN | 1 | ||
437 | SPC360SFNW | 12 | ||
438 | 4015-SVN | 1 | ||
439 | 3150-XER | 5 | ||
440 | 3220-XER | 3 | ||
441 | 3250DN-XER | 11 | ||
442 | 3250N-XER | 102 | ||
443 | 3315DN-XER | 1 | ||
444 | 3325DNI-XER | 7 | ||
445 | 3450-XER | 4 | ||
446 | 3500DN-XER | 18 | ||
447 | 3600DN-XER | 10 | ||
448 | 3600N-XER | 302 | ||
449 | 3655X-XER | 9 | ||
450 | 4250S-XER | 2 | ||
451 | 4400N-XER | 14 | ||
452 | 4500B-XER | 60 | ||
453 | 4510DT-XER | 1 | ||
454 | 4510N-XER | 565 | ||
455 | 4600DN-XER | 23 | ||
456 | 4620DT-XER | 16 | ||
457 | 4622DN-XER | 5 | ||
458 | 5335-XER | 1 | ||
459 | 5400-XER | 2 | ||
460 | 5500DT-XER | 14 | ||
461 | 5550DN-XER | 3 | ||
462 | 5550N-XER | 35 | ||
463 | 5845C-XER | 2 | ||
464 | 5855-XER | 1 | ||
465 | 6010N-XER | 4 | ||
466 | 6128MFP-XER | 1 | ||
467 | 6130N-XER | 2 | ||
468 | 6180N-XER | 89 | ||
469 | 6280N-XER | 209 | ||
470 | 6300N-XER | 2 | ||
471 | 6350DT-XER | 2 | ||
472 | 6360DX-XER | 5 | ||
473 | 6400X-XER | 2 | ||
474 | 6500N-XER | 33 | ||
475 | 6505N-XER | 6 | ||
476 | 6600DN-XER | 111 | ||
477 | 6605DN-XER | 2 | ||
478 | 6700DN-XER | 7 | ||
479 | 8560DN-XER | 5 | ||
480 | 8570DN-XER | 2 | ||
481 | C400DN-XER | 1 | ||
482 | C505X-XER | 1 | ||
483 | PE120I-XER | 1 | ||
484 | WC3210-XER | 2 | ||
485 | WC5325-XER | 14 | ||
LookUp |