Is VLOOKUP the best forumla?

[susieh1]

Hi All,

I am hoping you can assist me. I have a master spreadsheet which contains all orders which my sales team has closed for the year.

Master Data

Excel Workbook
	A	B	C	D	E	F	G	H
1	Week Ending	Close Date	Opportunity Owner	Type	Account Name	Product Type	Line Description	Total Price
2	29-Apr-11	4/24/2011	John Smith	Renewal	ABC Corp	Data	Renewal	19000
3	8-Apr-11	4/4/2011	Jane Brown	New Business	Brown Advisors	Papers	Premium	12500
4	15-Apr-11	4/12/2011	Vaughn Wheeler	Renewal	Smokey Mountain Paper	Newsletter	Renewal	4275
5	6-May-11	5/3/2011	Katie Hamilton	Renewal	Group Llc	Newsletter	Renewal	1095
6	13-May-11	5/13/2011	Bill Moore	New Business	Crown Group	Report	Standard	626.5
7	20-May-11	5/16/2011	Samatha Cook	Renewal	Acme Ltd.	Newsletter	Renewal	14705.55
Sheet1

Excel 2007

I have a second spreadsheet showing what each invididual sales person has for their quarterly target. I would like to populate their new business numbers from the data on the master spreadsheet.

Q2 New Business Targets

Excel Workbook
	A	B	C	D	E
1	Name	New Business Target Quarter	April New Business	May New Business	June New Business
2	John Smith	45,000.00
3	Jane Brown	35,000.00
4	Vaughn Wheeler	83,500.00
5	Katie Hamilton	45,000.00
6	Bill Moore	60,250.00
7	Samatha Cook	45,000.00
Sheet1

Excel 2007

Since I am constantly updaing the master spreadsheet I thought using a VLOOKUP for the sales person would be the best formula. However I can not seem to get it to work. Is that really the best way to go or does anyone have any suggestions?

Any thoughts or assistance would be greatly appreciated!
Susie

 

[delaneyjm]

VLOOKUP doesn't sum all of the matches together.

Given that you have multiple criteria, depending on your Excel version, you will need to use SUMPRODUCT or DSUM for 2003 and older versions, or SUMIFS for 2007 and newer versions.

 

[susieh1]

Thank you. I will give that a try and see if I have any luck.

 

[delaneyjm]

if you need assistance, just post back.