Vlookup?

Riddlemethis

New Member
Joined
Apr 20, 2021
Messages
3
Office Version
  1. 2013
Platform
  1. Windows
ok, this should be simple but it's wrecking my brain.

I've a large sheet of data which contains delivery's made to different locations but the amount delivered depends on whether or not a promotion is being run (usually every 3rd week). I'm looking to get an average of stock delivered in the 3 week cycle so i have a table of dates of when the delivery was made and a seperate table which tells me where the date fell in the 3 week cycle. ( Spreadsheet attached is a slim down version)

I'm using vlookup to check the date against the table array on the separate sheet and looking it to return the weekly cycle but keep getting N/A...

Should i be using vlookup? or am i doing something dumb here.
* my formula in coloum k

Delivery Test.xlsx
ABCDEFGHIJK
1DateDayStore No:Store NameTime DueArr TimeStart UnloadItem 1 Delivery amountItem 2 Delivery amountItem 3 Delivery amountCycle
206/04/2020Monday2Location 107:5007:1507:20131#N/A
306/04/2020Monday2Location 109:1509:1009:1513#N/A
420/04/2020Monday2Location 107:5507:4907:5393#N/A
520/04/2020Monday2Location 114:5011:3311:4513#N/A
627/04/2020Monday2Location 107:5007:5407:57101#N/A
727/04/2020Monday2Location 114:0012:1512:2020#N/A
804/05/2020Monday2Location 108:3008:1508:2081#N/A
904/05/2020Monday2Location 113:5510:5411:0019#N/A
1018/05/2020Monday2Location 108:0008:0708:1082#N/A
1118/05/2020Monday2Location 113:0011:5712:0010#N/A
1225/05/2020Monday2Location 107:5507:0007:0371#N/A
1325/05/2020Monday2Location 113:0011:1011:1519#N/A
1408/06/2020Monday2Location 107:5507:3507:4081#N/A
1508/06/2020Monday2Location 114:0011:3011:3513
1615/06/2020Monday2Location 107:5007:0507:0552
1715/06/2020Monday2Location 113:0512:2012:3020
1829/06/2020Monday2Location 108:0007:4507:5072
1929/06/2020Monday2Location 113:0012:1512:2018
2006/07/2020Monday2Location 107:4507:0707:1071
2106/07/2020Monday2Location 113:0011:4111:5027
2213/07/2020Monday2Location 108:0507:2007:552
2313/07/2020Monday2Location 113:5011:4012:003
2420/07/2020Monday2Location 107:5007:0507:1061
Deliveries
Cell Formulas
RangeFormula
K2:K14K2=VLOOKUP(A2,Cycle!$A$1:$B$476,2,FALSE)
B2:B24B2=TEXT(A2,"dddd")


Delivery Test.xlsx
AB
8929/03/2020Promo
9030/03/2020Promo
9131/03/2020Promo
9201/04/2020Promo
9302/04/2020Promo
9403/04/2020Promo
9504/04/2020Promo
9605/04/2020After Promo
9706/04/2020After Promo
9807/04/2020After Promo
9908/04/2020After Promo
10009/04/2020After Promo
10110/04/2020After Promo
10211/04/2020After Promo
10312/04/2020Pre-Promo
10413/04/2020Pre-Promo
10514/04/2020Pre-Promo
10615/04/2020Pre-Promo
10716/04/2020Pre-Promo
10817/04/2020Pre-Promo
10918/04/2020Pre-Promo
11019/04/2020Promo
11120/04/2020Promo
11221/04/2020Promo
11322/04/2020Promo
11423/04/2020Promo
11524/04/2020Promo
11625/04/2020Promo
11726/04/2020After Promo
11827/04/2020After Promo
11928/04/2020After Promo
12029/04/2020After Promo
12130/04/2020After Promo
12201/05/2020After Promo
12302/05/2020After Promo
12403/05/2020Pre-Promo
12504/05/2020Pre-Promo
12605/05/2020Pre-Promo
12706/05/2020Pre-Promo
12807/05/2020Pre-Promo
12908/05/2020Pre-Promo
13009/05/2020Pre-Promo
Cycle
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,747
Office Version
  1. 365
Platform
  1. MacOS
Are you sure the dates are real dates and 1 is not text
ON Both Sheets column A format to General and see if both provide just a number and if any decimals ?
Looks OK otherwise

and worked for me - did change the Dates format to general to make sure they are real dates

Book2
ABCDEFGHIJK
1DateDayStore No:Store NameTime DueArr TimeStart UnloadItem 1 Delivery amountItem 2 Delivery amountItem 3 Delivery amountCycle
243927Monday2Location 17:507:157:20131After Promo
343927Monday2Location 19:159:109:1513After Promo
443941Monday2Location 17:557:497:5393Promo
543941Monday2Location 114:5011:3311:4513Promo
643948Monday2Location 17:507:547:57101After Promo
743948Monday2Location 114:0012:1512:2020After Promo
843955Monday2Location 18:308:158:2081Pre-Promo
943955Monday2Location 113:5510:5411:0019Pre-Promo
1043969Monday2Location 18:008:078:1082#N/A
1143969Monday2Location 113:0011:5712:0010#N/A
1243976Monday2Location 17:557:007:0371#N/A
1343976Monday2Location 113:0011:1011:1519#N/A
1443990Monday2Location 17:557:357:4081#N/A
1543990Monday2Location 114:0011:3011:3513#N/A
1643997Monday2Location 17:507:057:0552#N/A
1743997Monday2Location 113:0512:2012:3020#N/A
1844011Monday2Location 18:007:457:5072#N/A
1944011Monday2Location 113:0012:1512:2018#N/A
2044018Monday2Location 17:457:077:1071#N/A
2144018Monday2Location 113:0011:4111:5027#N/A
2244025Monday2Location 18:057:207:552#N/A
2344025Monday2Location 113:5011:4012:003#N/A
2444032Monday2Location 17:507:057:1061#N/A
Sheet1
Cell Formulas
RangeFormula
B2:B24B2=TEXT(A2,"dddd")
K2:K24K2=VLOOKUP(A2,Cycle!$A$1:$B$476,2,FALSE)


Book2
AB
143919Promo
243920Promo
343921Promo
443922Promo
543923Promo
643924Promo
743925Promo
843926After Promo
943927After Promo
1043928After Promo
1143929After Promo
1243930After Promo
1343931After Promo
1443932After Promo
1543933Pre-Promo
1643934Pre-Promo
1743935Pre-Promo
1843936Pre-Promo
1943937Pre-Promo
2043938Pre-Promo
2143939Pre-Promo
2243940Promo
2343941Promo
2443942Promo
2543943Promo
2643944Promo
2743945Promo
2843946Promo
2943947After Promo
3043948After Promo
3143949After Promo
3243950After Promo
3343951After Promo
3443952After Promo
3543953After Promo
3643954Pre-Promo
3743955Pre-Promo
3843956Pre-Promo
3943957Pre-Promo
4043958Pre-Promo
4143959Pre-Promo
4243960Pre-Promo
Cycle
 
Solution

Riddlemethis

New Member
Joined
Apr 20, 2021
Messages
3
Office Version
  1. 2013
Platform
  1. Windows
Thanks for looking at this.

I cannot for some reason get the cells in the first spreadsheet to change to general format. The second sreadsheet is fine but when i change the formatting of the first spreadsheet it still shows in date format (even tho saying its general). How did you change the first spreadsheet? I've tried copying and pasting just values and tried putting them back in and changing format but still no luck?
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,747
Office Version
  1. 365
Platform
  1. MacOS
if you change to general - and still date then that text
TRY
using Text to Columns on the column and see of that now changes the dates as text to the dates as real dates and numbers
 

Riddlemethis

New Member
Joined
Apr 20, 2021
Messages
3
Office Version
  1. 2013
Platform
  1. Windows
if you change to general - and still date then that text
TRY
using Text to Columns on the column and see of that now changes the dates as text to the dates as real dates and numbers
Perfect! It worked.:)
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,747
Office Version
  1. 365
Platform
  1. MacOS
you are welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,132,702
Messages
5,654,820
Members
418,155
Latest member
demasisi

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