Adding together results of multiple VLOOKUP results

meppwc

Well-known Member
Joined
May 16, 2003
Messages
604
Office Version
  1. 365
Platform
  1. Windows
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?

IB_CONTR_Asset_Listing_Account_Number_Report_MS_L1 (2).xls
ABCDEFG
1Model/MakeACC_DESCPart#/EDP
21000Z-HEW[XXXX]HP NO 41 GENUINE TRICOL DJ 820 850 855 87051641A-DS1000Z-HEW
31000Z-HEW[XXXX]REMAN INK CART HP 41 51641A TRI-COLOR51641A-RM-DS1000Z-HEW
41000Z-HEWHP 45 BLACK INK CART 930 PAGE YIELD51645A-DS1000Z-HEW6122-HEW990CXI-HEW17
BOM


IB_CONTR_Asset_Listing_Account_Number_Report_MS_L1 (2).xls
AB
1CFXL40001
2D3201
3IP36001
4IRC5540I-CAN1
5L1002
6L190-CAN1
7L806
8L907
9MP830-CAN1
10MX3403
11PC9201
12TR8520-CAN1
13C1660W-DEL1
144630-EPS1
15WF3620-EPS2
16WF3640-EPS1
171000Z-HEW4
181012-HEW10
191020Z-HEW1
201040-HEW1
211100Z-HEW1
221150-HEW1
231160-HEW2
241200-HEW24
251300-HEW8
261320-HEW32
271512-HEW1
281600-HEW4
292015D-HEW16
302100-HEW1
312200DTN-HEW2
322280-HEW1
332300-HEW6
342420-HEW3
352430-HEW8
362430N-HEW1
372512-HEW1
38251DW-HEW2
392600N-HEW3
402605-HEW1
41276DW-HEW1
422840-HEW3
433050-HEW1
443055MFP-HEW2
453520-HEW1
463800-HEW24
473830-HEW1
484000-HEW18
494050-HEW45
504050TN-HEW1
514200-HEW51
524250N-HEW158
534300-HEW7
544350-HEW6
554500IJ-HEW3
564520-HEW1
574600DNM-HEW5
584650-HEW4
594700DTN-HEW1
604700N-HEW8
614PLUS-HEW1
625440-HEW1
635500-HEW1
645550DN-HEW7
655650-HEW1
665-HEW3
675N-HEW1
686100-HEW6
696230-HEW17
706305-HEW1
716310-HEW2
726940-HEW6
736968-HEW1
746978-HEW1
756980-HEW3
767155-HEW1
777210-HEW1
788000-HEW4
798000N-HEW2
808000Z-HEW1
818025-HEW1
828100E-HEW16
838100-HEW2
848150-HEW6
858210-HEW125
868500C-HEW2
878600-HEW10
888610-HEW2
898620-HEW1
908700-HEW1
918710-HEW6
928720-HEW2
938730-HEW3
948740-HEW1
959010-HEW2
969020-HEW4
979050N-HEW4
98940C-HEW1
99950XI-HEW1
1009800-HEW1
101990CXI-HEW3
102C4680-HEW1
103CM1415FN-HEW5
104CM2320FXI-HEW9
105CM4540F-HEW1
106CM4540-HEW3
107CM4730-HEW2
108CM6040-HEW1
109CP1025NW-HEW2
110CP1215-HEW9
111CP1525NW-HEW6
112CP2025-HEW69
113CP3525DN-HEW11
114CP3525X-HEW1
115CP4025N-HEW2
116CP4525N-HEW3
117CP6015X-HEW1
118D110A-HEW1
119D2460-HEW1
120DJ6940-HEW7
121DX2000-HEW1
122HP2050-HEW1
123HP4005-HEW1
124HP4240-HEW1
125J4580-HEW1
126K5400-HEW1
127K5400TN-HEW1
128K550DTN-HEW3
129L7580-HEW1
130LJ3052-HEW1
131M102W-HEW9
132M1212NF-HEW6
133M127FN1
134M130FW-HEW1
135M1522NF-HEW4
136M201DW-HEW4
137M225DN-HEW1
138M251NW-HEW3
139M252DW-HEW3
140M254DW-HEW7
141M2727NF-HEW4
142M276NW-HEW5
143M277DW-HEW7
144M281FDW-HEW3
145M3035-HEW2
146M375NW-HEW1
147M401DNE-HEW128
148M402DNE-HEW84
149M402N-HEW8
150M404DN-HEW10
151M425DN-HEW11
152M426FDN-HEW16
153M428FDN-HEW2
154M4345-HEW4
155M451NW-HEW61
156M452DN-HEW42
157M454DN-HEW11
158M4555H-HEW4
159M475DN-HEW6
160M476DN-HEW25
161M477FDN-HEW7
162M477FDW-HEW1
163M477FNW-HEW180
164M479FDW-HEW19
165M501DN-HEW46
166M5035-HEW1
167M506DN-HEW438
168M507N-HEW50
169M521DN-HEW2
170M525F-HEW12
171M527F-HEW34
172M528F-HEW2
173M551DN-HEW25
174M551N-HEW1
175M553N-HEW448
176M570DN-HEW3
177M575C-HEW17
178M577DN-HEW1
179M577F-HEW28
180M602DN-HEW103
181M603DN-HEW30
182M603N-HEW1
183M604N-HEW13
184M605DN-HEW189
185M606DN-HEW26
186M607DN-HEW31
187M607N-HEW1
188M608DN-HEW74
189M630F-HEW5
190M651N-HEW5
191M653DN-HEW2
192M681Z-HEW1
193M806DN-HEW4
194OJ7612-HEW1
195P1005-HEW3
196P1006-HEW2
197P1102W-HEW20
198P1505-HEW42
199P1606DN-HEW18
200P2035-HEW115
201P2055D-HEW114
202P3005D-HEW34
203P3010-HEW6
204P3015-HEW217
205P4014-HEW26
206P4515N-HEW82
207X476DN-HEW6
208X576DW-HEW1
209X585DN-HEW3
210MS315DN-IBM57
211MS825DN-IBM42
212XM5170-IBM77
2131352-IBM2
214C543DN-IBM1
215C734N-IBM2
216C746DN-IBM10
217CS310N-IBM1
218CS410DN-IBM4
219CS510DE-IBM2
220CX410E-IBM2
221E260D-IBM1
222E260DN-IBM10
223E340-IBM4
224E350D-IBM2
225E352DN-IBM1
226E360DN-IBM24
227E460DN-IBM1
228MS310DN-IBM12
229MS312DN-IBM8
230MS321DN-IBM2
231MS410DN-IBM3
232MS415DN-IBM7
233MS510DN-IBM3
234MS610DN-IBM8
235MS710DN-IBM1
236MS810DTN-IBM51
237MS810N-IBM1
238MS811DN-IBM88
239MS812DN-IBM166
240MS823DN-IBM1
241MX310DN-IBM1
242MX511DE-IBM7
243MX710DHE-IBM39
244S1250-IBM2
245S815-IBM1
246T620Y-IBM1
247T632-IBM1
248T634-IBM3
249T640-IBM6
250T644-IBM32
251T650N-IBM6
252T652N-IBM1
253T654DT-IBM248
254T654DTN-IBM5
255X204N-IBM1
256X364DN-IBM1
257X466DE-IBM27
258X544DTN-IBM2
259X656DE-IBM40
260XC4150-IBM2
2611022-HEW21
2621125-DEL2
2631270E-BRT3
2641720-DEL1
2651860C-BRT2
2662165W-SSG1
2672350DN-DEL1
2682820-BRT38
2692840-BRT30
2702920-BRT9
2712940-BRT5
2723055MFP-HEW2
2733110CN-DEL1
2743300MFP-XER3
2753550-HEW1
2764100-BRT112
2774100E-BRT1
2784100-HEW40
279450-DYM1
2804750-BRT1
2814750E-BRT84
2825100TN-HEW2
2835750E-BRT3
284575-BRT1
2856122-HEW10
2866988-HEW4
287B2360DN-DEL1
288B410D-OKI1
289B4350-OKI3
290B4600-OKI3
291B6300-OKI3
292B6500-OKI10
293B710DN-OKI10
294B720DN-OKI7
295B721DN-OKI13
296C1760NW-DEL3
297C530DN-OKI1
298C6150DN-OKI1
299C711DN-OKI2
300CLP325W-SSG1
301CLX3170FN-SSG1
302CM1312NFI-HEW8
303CM3530FS-HEW1
304CP1518NI-HEW5
305CP3505-HEW12
306DCP7020-BRT1
307DCP7030-BRT2
308DCP7040-BRT3
309DCP7065DN-BRT4
310GK420T-ZEB4
311GX420D-ZEB8
312HL2040-BRT1
313HL2070N-BRT1
314HL2140-BRT20
315HL-2170W-BRT3
316HL2220-BRT2
317HL2230-BRT1
318HL-2240D-BRT30
319HL2270DW-BRT3
320HL3040CN-BRT2
321HL3140CW-BRT3
322HL5070N-BRT1
323HL5150D-BRT1
324HL5250DN-BRT3
325HL5340D-BRT5
326HL5450DN-BRT3
327HLL2300D-BRT2
328HLL2320D-BRT3
329HLL5000D-BRT1
330HLL6200DW-BRT1
331HP COLLJ 3600N5
332HPCOLSJET35001
333HPLJETM1536DNF13
334HPLSJET28201
335L2340DW-BRT2
336L2360DW-BRT10
337L2370DW-BRT2
338L2685DW-BRT1
339LEXMARK C9351
340LJET 600 M60123
341LP2824-ZEB1
342M2020W-SSG2
343MC3326ADWE-IBM1
344MFC4800-BRT1
345MFC5490CN-BRT1
346MFC6490CW-BRT1
347MFC7220-BRT4
348MFC7240-BRT3
349MFC7365DN-BRT1
350MFC7420-BRT1
351MFC7440N-BRT2
352MFC7820N-BRT1
353MFC7840W-BRT4
354MFC7860DW-BRT2
355MFC8120-BRT43
356MFC-8220-BRT30
357MFC8460N-BRT3
358MFC8710DW-BRT1
359MFC8890DW-BRT1
360MFC8950DW-BRT3
361MFC9130CW-BRT1
362MFC9320CW-BRT1
363MFC9340CDW-BRT1
364MFC9700-BRT2
365MFCJ825DW-BRT2
366ML-2250-SSG1
367ML-2950ND-SSG1
368ML-2955DW-SSG1
369ML3750ND-SSG5
370ML4020ND-SSG1
371MS725DVN-IBM1
372P4015-HEW128
373QLN420-ZEB10
374S4M-ZEB1
375SCX4623F-SSG3
376SCX5639FR-SSG1
377SP8300DN-MICR1
378TLP2824PLUS-ZEB1
379UF6200-PAN3
380UF770-PAN4
381WF3520-EPS1
382ZD4103
383ZD41H22-ZEB18
384ZD420T-ZEB1
385ZT230-ZEB4
386DX800-PAN1
387KXFHD351-PAN2
388KXFL511-PAN2
389UF490-PAN1
390UF790-PAN1
391UF7950-PAN1
392UF-8000-PAN1
393UF8200-PAN3
394UF895-PAN1
395CL7200D2
396IM350F863
397IM430FTL1
398IM550F38
399IMC2000307
400IMC3000642
401IMC300F80
402IMC4500116
403IMC600016
404MC250FWB3
405MP171SPF1
406MP201SPF1
407MP301SPF43
408MP4001SP1
409MP4002SP2
410MP4054SP1
411MP501SPF1
412MP6503SP5
413MPC300321
414MPC30041
415MPC305SPF2
416MPC306SPF7
417MPC30759
418MPC45035
419MPC55031
420NA1
421P50123
422P501TL37
423P80014
424P8013
425PC301W14
426PC60024
427SP330DN1
428SP330SFN1
429SP3500SF1
430SP3710DN7
431SP3710SF14
432SP4100N1
433SP4100NL1
434SP4310N-RIC2
435SP8400DN1
436SPC342DN1
437SPC360SFNW12
4384015-SVN1
4393150-XER5
4403220-XER3
4413250DN-XER11
4423250N-XER102
4433315DN-XER1
4443325DNI-XER7
4453450-XER4
4463500DN-XER18
4473600DN-XER10
4483600N-XER302
4493655X-XER9
4504250S-XER2
4514400N-XER14
4524500B-XER60
4534510DT-XER1
4544510N-XER565
4554600DN-XER23
4564620DT-XER16
4574622DN-XER5
4585335-XER1
4595400-XER2
4605500DT-XER14
4615550DN-XER3
4625550N-XER35
4635845C-XER2
4645855-XER1
4656010N-XER4
4666128MFP-XER1
4676130N-XER2
4686180N-XER89
4696280N-XER209
4706300N-XER2
4716350DT-XER2
4726360DX-XER5
4736400X-XER2
4746500N-XER33
4756505N-XER6
4766600DN-XER111
4776605DN-XER2
4786700DN-XER7
4798560DN-XER5
4808570DN-XER2
481C400DN-XER1
482C505X-XER1
483PE120I-XER1
484WC3210-XER2
485WC5325-XER14
LookUp
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
I think I figured it out. Easier than I thought
=VLOOKUP(D4,LookUp!$A:$B,2,FALSE)+VLOOKUP(E4,LookUp!$A:$B,2,FALSE)+VLOOKUP(F4,LookUp!$A:$B,2,FALSE)
 
Upvote 0
A simpler way would be
Excel Formula:
=SUM(VLOOKUP(D2#,Lookup!$A$1:$B$485,2,0))
 
Upvote 0
Oh man. That is clean.........and alot more simple. LOL
Thanks again
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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