VLookup and Match

awhiterussian

New Member
Joined
Jul 27, 2007
Messages
10
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!
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

ExcelChampion

Well-known Member
Joined
Aug 12, 2005
Messages
976
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
ABCDEFG
1LookupTableCriteria
2NameWeekNameWeekFormula
3John1Bob6*
4Bob6
5Bill4
6Todd7
7Darren2
8Bob3
9Todd9
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

New Member
Joined
Jul 27, 2007
Messages
10
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

New Member
Joined
Jul 27, 2007
Messages
10
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

Board Regular
Joined
Jul 28, 2007
Messages
96
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

New Member
Joined
Jul 27, 2007
Messages
10
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

Board Regular
Joined
Jul 28, 2007
Messages
96
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

New Member
Joined
Jul 27, 2007
Messages
10
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

Board Regular
Joined
Jul 28, 2007
Messages
96
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

New Member
Joined
Jul 27, 2007
Messages
10
REAL East file with promos TEST Good 073007R1.xls
BCDE
2PromotionCalendar
3
4ConcatenationofCustomerandProductStartDateEndDate
5A&P/FOODMART00004ARIZONAICEDTEAHG01/21/0702/22/07
6A&P/FOODMART00004AXELRODCOTTAGECHEESE16OZ04/21/0705/18/07
7A&P/FOODMART00004AXELRODCOTTAGECHEESE16OZ06/09/0707/05/07
8A&P/FOODMART00004AXELRODCOTTAGECHEESE16OZ07/28/0708/23/07
9
10ShipmentCalendar
11ConcatenationofCustomerandProductShipDateCasesShippedOnPromotion?
12A&P/FOODMART00004ARIZONAICEDTEAHG01/21/0775FORMULA
13A&P/FOODMART00004CARBCOUNTDOWNHG-6CS01/07/06136
14A&P/FOODMART00004CARBCOUNTDOWNHG-6CS01/28/0651
15A&P/FOODMART00004LACTAIDFLUIDMILK32OZ-12CS01/28/06255
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.
 

Forum statistics

Threads
1,181,658
Messages
5,931,269
Members
436,785
Latest member
KingGideon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top