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!

 

[ExcelChampion]

Not enough info...

What does the lookup table look like? Is the customer name and week both in column M or separated by differnt columns?

Try to work through this example:

Book1
	A	B	C	D	E	F	G
1	LookupTable				Criteria
2	Name	Week			Name	Week	Formula
3	John	1			Bob	6	*
4	Bob	6
5	Bill	4
6	Todd	7
7	Darren	2
8	Bob	3
9	Todd	9
Sheet1

formula in G3:

=IF(ISNA(MATCH(E3&"@"&F3,A3:A9&"@"&B3:B9,0)),0,"*")

Must be confirmed with Ctrl+shift+Enter, not just Enter.

 

[awhiterussian]

well, i believe we're half way there. the problem is instead of "week 6" as a lookup value i have something like "01/27/2007" and i'm trying to match that in a range of dates.

The concept behind it is this: The promotion calendar looks like this:

Account Product Start Date End Date

Kroger Milk 01/2/2007 01/25/2007
Walmart yogurt 02/25/2007 03/05/2007
Albertsons milk 03/15/2007 03/20/2007

Now each specific deal refers to a specific customer and product. So, i'm matching this to the shipment data that looks like this:

Account Product Cases Shipped Date

Kroger milk 400 03/25/2007
Walmart yogurt 350 04/15/2007


So what i'm trying to do is from the shipment data to look up and see if that specific product was delivered to the customer was on promotion. So i'm looking up the shipment date in an interval of dates in the promotion calendar and if the shipment date falls in that interval, then it should return an asterisk

Hope thats specific enough.

Thanks again! Just learning this whole logic of excel business and I appreciate your help.

Seva

 

[awhiterussian]

This is my formula so far:

=IF(ISNA(MATCH(J15&"@"&L15,Sheet1!B7:B478&"@"*AND((L15>=MIN('East Data'!AV7,'East Data'!AW7)),L15<=MAX('East Data'!AV7,'East Data'!AW7)))),0,"*")

i think something is wrong with it though.

I spliced the formula you gave me with a formula that shows whether a date is within a certain range of dates.

 

[cpis0002]

I like simplifying things, and i think if you dnt mind adding to columns (which can be hidden) then add to 2 columns in the shipping data sheet, with a Lookup formula in each (or Vlookup if you prefer).

So, one column will lookup the start date from the promo calendar, and the 2nd will lookup the end date. then wherever you want the asterick, make a formula like the following:

=IF(AND(shipdate>=startdate,shipdate<=enddate),"*","")

and if you want to show off abit make it return "o" instead of "*", (make sure its not caps), and set the font to Webdings... you'll get a picture of a boat, lol... you can play around, der r pictures of planes, and letterboxes, just choose one most appropriate to ure business.

 

[awhiterussian]

the problem is i'm matching 2 columns and returning a 3rd, the 3rd being whether or not the date of shipped product is in the range of promo dates.

your formula is incomplete because i have 1000 customers and 100 products and i need to match them before i do a vlookup for the dates.

 

[cpis0002]

I don't see why its not enough.

The method I described will get the promo period for that specific customer, and if the shipping date of that order is between the promo period for that customer, it will put an astericks... isn't that what you needed?

If that is what you want, but maybe didnt understand how to implement it, or need something else, just let me know.

You could also email me with your needs and a copy of the file to chrispisani@gmail.com and i'll do my best to help

 

[awhiterussian]

ya, but there is a 3rd variable and that is product.

you have product, customer, and date.

you said "get the promo period for that specific customer"... true but there are promotions running on different products at the same time for the same customer.

 

[cpis0002]

Ah, so lets say Walmart will have more than one product... like lets say milk, and yogurt... but can you have 2 Walmarts, both with Yogurt, and different promo dates?

It's alot easier if i c it and play around :P

 

[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.