Vlookup?

[Riddlemethis]

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
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Day	Store No:	Store Name	Time Due	Arr Time	Start Unload	Item 1 Delivery amount	Item 2 Delivery amount	Item 3 Delivery amount	Cycle
2	06/04/2020	Monday	2	Location 1	07:50	07:15	07:20	13	1		#N/A
3	06/04/2020	Monday	2	Location 1	09:15	09:10	09:15			13	#N/A
4	20/04/2020	Monday	2	Location 1	07:55	07:49	07:53	9	3		#N/A
5	20/04/2020	Monday	2	Location 1	14:50	11:33	11:45			13	#N/A
6	27/04/2020	Monday	2	Location 1	07:50	07:54	07:57	10	1		#N/A
7	27/04/2020	Monday	2	Location 1	14:00	12:15	12:20			20	#N/A
8	04/05/2020	Monday	2	Location 1	08:30	08:15	08:20	8	1		#N/A
9	04/05/2020	Monday	2	Location 1	13:55	10:54	11:00			19	#N/A
10	18/05/2020	Monday	2	Location 1	08:00	08:07	08:10	8	2		#N/A
11	18/05/2020	Monday	2	Location 1	13:00	11:57	12:00			10	#N/A
12	25/05/2020	Monday	2	Location 1	07:55	07:00	07:03	7	1		#N/A
13	25/05/2020	Monday	2	Location 1	13:00	11:10	11:15			19	#N/A
14	08/06/2020	Monday	2	Location 1	07:55	07:35	07:40	8	1		#N/A
15	08/06/2020	Monday	2	Location 1	14:00	11:30	11:35			13
16	15/06/2020	Monday	2	Location 1	07:50	07:05	07:05	5	2
17	15/06/2020	Monday	2	Location 1	13:05	12:20	12:30			20
18	29/06/2020	Monday	2	Location 1	08:00	07:45	07:50	7	2
19	29/06/2020	Monday	2	Location 1	13:00	12:15	12:20			18
20	06/07/2020	Monday	2	Location 1	07:45	07:07	07:10	7	1
21	06/07/2020	Monday	2	Location 1	13:00	11:41	11:50			27
22	13/07/2020	Monday	2	Location 1	08:05	07:20	07:55	2
23	13/07/2020	Monday	2	Location 1	13:50	11:40	12:00			3
24	20/07/2020	Monday	2	Location 1	07:50	07:05	07:10	6	1
Deliveries

Cell Formulas
Range	Formula
K2:K14	K2	=VLOOKUP(A2,Cycle!$A$1:$B$476,2,FALSE)
B2:B24	B2	=TEXT(A2,"dddd")

Delivery Test.xlsx
	A	B
89	29/03/2020	Promo
90	30/03/2020	Promo
91	31/03/2020	Promo
92	01/04/2020	Promo
93	02/04/2020	Promo
94	03/04/2020	Promo
95	04/04/2020	Promo
96	05/04/2020	After Promo
97	06/04/2020	After Promo
98	07/04/2020	After Promo
99	08/04/2020	After Promo
100	09/04/2020	After Promo
101	10/04/2020	After Promo
102	11/04/2020	After Promo
103	12/04/2020	Pre-Promo
104	13/04/2020	Pre-Promo
105	14/04/2020	Pre-Promo
106	15/04/2020	Pre-Promo
107	16/04/2020	Pre-Promo
108	17/04/2020	Pre-Promo
109	18/04/2020	Pre-Promo
110	19/04/2020	Promo
111	20/04/2020	Promo
112	21/04/2020	Promo
113	22/04/2020	Promo
114	23/04/2020	Promo
115	24/04/2020	Promo
116	25/04/2020	Promo
117	26/04/2020	After Promo
118	27/04/2020	After Promo
119	28/04/2020	After Promo
120	29/04/2020	After Promo
121	30/04/2020	After Promo
122	01/05/2020	After Promo
123	02/05/2020	After Promo
124	03/05/2020	Pre-Promo
125	04/05/2020	Pre-Promo
126	05/05/2020	Pre-Promo
127	06/05/2020	Pre-Promo
128	07/05/2020	Pre-Promo
129	08/05/2020	Pre-Promo
130	09/05/2020	Pre-Promo
Cycle

 

[etaf]

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
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Day	Store No:	Store Name	Time Due	Arr Time	Start Unload	Item 1 Delivery amount	Item 2 Delivery amount	Item 3 Delivery amount	Cycle
2	43927	Monday	2	Location 1	7:50	7:15	7:20	13	1		After Promo
3	43927	Monday	2	Location 1	9:15	9:10	9:15			13	After Promo
4	43941	Monday	2	Location 1	7:55	7:49	7:53	9	3		Promo
5	43941	Monday	2	Location 1	14:50	11:33	11:45			13	Promo
6	43948	Monday	2	Location 1	7:50	7:54	7:57	10	1		After Promo
7	43948	Monday	2	Location 1	14:00	12:15	12:20			20	After Promo
8	43955	Monday	2	Location 1	8:30	8:15	8:20	8	1		Pre-Promo
9	43955	Monday	2	Location 1	13:55	10:54	11:00			19	Pre-Promo
10	43969	Monday	2	Location 1	8:00	8:07	8:10	8	2		#N/A
11	43969	Monday	2	Location 1	13:00	11:57	12:00			10	#N/A
12	43976	Monday	2	Location 1	7:55	7:00	7:03	7	1		#N/A
13	43976	Monday	2	Location 1	13:00	11:10	11:15			19	#N/A
14	43990	Monday	2	Location 1	7:55	7:35	7:40	8	1		#N/A
15	43990	Monday	2	Location 1	14:00	11:30	11:35			13	#N/A
16	43997	Monday	2	Location 1	7:50	7:05	7:05	5	2		#N/A
17	43997	Monday	2	Location 1	13:05	12:20	12:30			20	#N/A
18	44011	Monday	2	Location 1	8:00	7:45	7:50	7	2		#N/A
19	44011	Monday	2	Location 1	13:00	12:15	12:20			18	#N/A
20	44018	Monday	2	Location 1	7:45	7:07	7:10	7	1		#N/A
21	44018	Monday	2	Location 1	13:00	11:41	11:50			27	#N/A
22	44025	Monday	2	Location 1	8:05	7:20	7:55	2			#N/A
23	44025	Monday	2	Location 1	13:50	11:40	12:00			3	#N/A
24	44032	Monday	2	Location 1	7:50	7:05	7:10	6	1		#N/A
Sheet1

Cell Formulas
Range	Formula
B2:B24	B2	=TEXT(A2,"dddd")
K2:K24	K2	=VLOOKUP(A2,Cycle!$A$1:$B$476,2,FALSE)

Book2
	A	B
1	43919	Promo
2	43920	Promo
3	43921	Promo
4	43922	Promo
5	43923	Promo
6	43924	Promo
7	43925	Promo
8	43926	After Promo
9	43927	After Promo
10	43928	After Promo
11	43929	After Promo
12	43930	After Promo
13	43931	After Promo
14	43932	After Promo
15	43933	Pre-Promo
16	43934	Pre-Promo
17	43935	Pre-Promo
18	43936	Pre-Promo
19	43937	Pre-Promo
20	43938	Pre-Promo
21	43939	Pre-Promo
22	43940	Promo
23	43941	Promo
24	43942	Promo
25	43943	Promo
26	43944	Promo
27	43945	Promo
28	43946	Promo
29	43947	After Promo
30	43948	After Promo
31	43949	After Promo
32	43950	After Promo
33	43951	After Promo
34	43952	After Promo
35	43953	After Promo
36	43954	Pre-Promo
37	43955	Pre-Promo
38	43956	Pre-Promo
39	43957	Pre-Promo
40	43958	Pre-Promo
41	43959	Pre-Promo
42	43960	Pre-Promo
Cycle

 

[Riddlemethis]

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]

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]

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]

you are welcome