How do I SUM multiple results from a VLOOKUP?

  • Thread starter Thread starter garnm2
  • Start date Start date
G

garnm2

I am using a VLOOKUP to look up the name of an employee and return the hours
they worked on all projects for each day. However an employee is able to
work on multiple projects, which causes for multiple entries in the vlookup
range. How do I get it to sum these numbers and provide me with one result?
Currently it is returning the first result found in the range.

I am currently using the following VLOOKUP formula:
=VLOOKUP($B14,Mon!$D$4:$Q$100,13,0).

Any help is greatly appreciated!!
Melissa
 
From you formula, if seems that in the table D4:Q100 of worksheet Mon:
column D has the name/id that you are looking for (the value in B14)
and Q has the data to be summed. I would have thought
=VLOOKUP($B14,Mon!$D$4:$Q$100,14,0 (fourteen not thirteen) would have found
one value.

Of course, VLOOKUP find the first value only. To find multiple values use
=SUMPRODUCT(--(Mon!D4:D100=B14),Mon!Q4:Q100)
I will leave you to pepper this with $s

For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html

best wishes
 
Back
Top