Tricky Lookup/Find Question

[Scottbud]

Hi, I am hopeing that this is an easy question for one of you. Allthough I have mastered vlookup's I dont know how to takle this particular problem.

I am a product manager and am trying to make an anylisis workbook.
On one sheet (Promotions Forecast) I have each appropriate month listed in the first row, Aug-11, Sept-11, Oct-11, Nov-11, Dec-11 and so on.

In the first column I have all the product codes.

Then the data area contains quantities we expect to sell during that month for each product. I would like to be able to keep the historical data on the sheet


In another sheet (Analysis)

I want to dynamically return (in column AM) the the total expected sales for each product for the next 3 months.
Product Codes are in Column A on the Analysis sheet.
Help :S

 

[bensonsearch]

Hi, as far as I know you would need to do multiple vlookups and add them up.

ie:

=(vlookup(stuff)+vlookup(stuff)+vlookup(stuff))

adjust as needed (may need more brackets)

 

[Scottbud]

Thanks for your help, I thought about that option but had hoped there was an easier way as I would have to either move all the data around or change the formulas each time we click over into a new month.

 

[bensonsearch]

im not that great with formulas but you could use the MONTH or similar formulas in there (instead of saying Oct11 put MONTH & "11") and also (MONTH - 2) & "11"

like i said should work but I havent tested yet

 

[Scottbud]

Thanks :D

 

[Scottbud]

Sorted it! Thanks for your help :D

It looked a little something like this.
=VLOOKUP(A3,Promos,MATCH($AM$2,'Promotions Forecast'!$3:$3,0),FALSE)

 

[bensonsearch]

Hi, no issues glad to help

 

[snoopyhr]

Having the product list in column A and forecast for sales in column B to say AL, in sheet 1 and then product list and desired total forecast sale for 3 months in sheet 2 (adjust range as desired)

See if this gets you started :

Excel Workbook
	A	B	C	D	E	F	G	H
4	Product	Sep-11	Oct-11	Nov-11	Dec-11	Jan-12	Feb-12	Mar-12
5	Product 1	167	51	146	173	80	82	43
Sheet1

Excel Workbook
	A	B
1	Start	Jan-12
2	End	Mar-12
3	Product	*
4	Product 1	205
Sheet2

#VALUE!

</td></tr></table></td></tr></table>