VLookup and Match

[awhiterussian]

so here is the problem:

I have 2 spreadsheets. One is a promotion calendar that lists the dates that promotions on a certain product runs. The other is a shipment grid of shipments of that product to the customer.

I want VLookup to find the customer and the dates and then bring me back an asterisk in a separate column to show me that that certain week that product was delivered was a promotion week. The problem I have is using Vlookup to lookup 2 things at once (and if they match to the promotion calendar) and return me an asterisk.

Here is my formula now:

=IF(VLOOKUP(J2&" "&L2,'East Data'!M:AU,2,FALSE),"*",0)

J2 is the customer name
L2 is the week
"East Data" is the spreadsheet with all of the promotions and customers.

Thanks for your help in advance!

 

[awhiterussian]

REAL East file with promos TEST Good 073007R1.xls
	B	C	D	E
2	PromotionCalendar
3
4	ConcatenationofCustomerandProduct	StartDate	EndDate
5	A&P/FOODMART00004ARIZONAICEDTEAHG	01/21/07	02/22/07
6	A&P/FOODMART00004AXELRODCOTTAGECHEESE16OZ	04/21/07	05/18/07
7	A&P/FOODMART00004AXELRODCOTTAGECHEESE16OZ	06/09/07	07/05/07
8	A&P/FOODMART00004AXELRODCOTTAGECHEESE16OZ	07/28/07	08/23/07
9
10	ShipmentCalendar
11	ConcatenationofCustomerandProduct	ShipDate	CasesShipped	OnPromotion?
12	A&P/FOODMART00004ARIZONAICEDTEAHG	01/21/07	75	FORMULA
13	A&P/FOODMART00004CARBCOUNTDOWNHG-6CS	01/07/06	136
14	A&P/FOODMART00004CARBCOUNTDOWNHG-6CS	01/28/06	51
15	A&P/FOODMART00004LACTAIDFLUIDMILK32OZ-12CS	01/28/06	255
Sheet1

Here is an example of a spreadsheet. The problem is I have over 1000 stores and 100 products and a year and a half of dates.

i think the formula requires the "if(match(" combo to work. I am having trouble to write a logic statement that makes it search to see if the shipment date falls in between one of the promotion dates to return me an asterisk.

 

[ExcelChampion]

Hmm...I hate using SUMPRODUCT here, but it seems like the simplest solution:

Book1
	A	B	C	D	E
1	Account	Product	StartDate	EndDate
2	Kroger	Milk	1/2/2007	1/25/2007
3	Walmart	yogurt	2/25/2007	3/5/2007
4	Albertsons	milk	3/15/2007	3/20/2007
5
6
7	Account	Product	Cases	ShippedDate	FORMULA
8	Kroger	milk	400	1/10/2007	*
9	Walmart	yogurt	350	4/15/2007
Sheet1

Formula in E8:
=IF(SUMPRODUCT(--(A8=$A$2:$A$4),--(B8=$B$2:$B$4),--(D8>=$C$2:$C$4),--(D8<=$D$2:$D$4)),"*","")