Macro or VLookup? - Current VLookup Not Working

[JFray]

Hi! I am new to VBA & Macros and didn't think I would get stuck on Vlookup, but here I am. I cannot figure out why it is not pulling in my date field from sheet 2. I have tried numerous things to try to make it work, but now seems like I am running in circles. Maybe a macro would be the better way to go with this request, but I just don't know how to even start. I've added a mini sheet below from "AUKYSCHEX" (aka sheet 1) so you can see what I am trying to do and "AUKYSCHEX1 (aka sheet 2)
I pull this data from 2 different reports that I run and paste into these sheets (columns A:M in sheet 1 and columns A:E in sheet 2). In AUKYSCHEX sheet 1 I added column N, using =right(M2,7) formula to extract the 7 digits from the text field. Followed by formatting column N to be number. Then concatenated column N and column F to make a distinct number to use for my Vlookup function in column P. Vlookup is to look in "AUKYSCHEX1" and return the date for the cell reference in column O in the 'AUKYSCHEX' sheet. I've tried cutting and pasting the lookup columns to the A position. I've tried removing formatting to see if I went to far with the formatting. Nothing has worked so far. Any help with this would be GREATLY Appreciated!!!!

Schedules Template.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Line	WO No	Priority	Start Date	Part No	Description	WO Quantity	Qty Comp	UOM	Run Hrs	Req/Prom	SO Text	SO No	SO + Item	Ship Date
2	FCT417A04	83262760		04/13/21	1629642	DH276 BLU 455 14gsmX850MM	24,760	0	LB	23.27	04/15/21	07527600	7527600	75276001629642	#N/A
3	FCT417A04	83262880		04/14/21	1629642	DH276 BLU 455 14gsmX850MM	24,760	0	LB	23.27	04/16/21	07527652	7527652	75276521629642	#N/A
4	FCT417A04	83228928		04/15/21	1811977	DH276 BL399.5X23.25 31.5" OD	40,000	0	LB	32.8	04/17/21	07510685	7510685	75106851811977	#N/A
5	FCT417A04	83228931		04/16/21	1811977	DH276 BL399.5X23.25 31.5" OD	40,000	0	LB	32.8	04/19/21	07510710	7510710	75107101811977	#N/A
6	FCT417A04	83228950		04/18/21	1811977	DH276 BL399.5X23.25 31.5" OD	40,000	0	LB	32.8	04/20/21	07510723	7510723	75107231811977	#N/A
7	FCT417A05	82823007		04/09/21	1651453	4013 WHT 50GSMX970 A5 AV	20,000	0	LB	24	04/11/21	06871929	6871929	68719291651453	#N/A
8	FCT417A05	82823010	B	04/10/21	1651453	4013 WHT 50GSMX970 A5 AV	20,000	0	LB	24	04/12/21	06871929	6871929	68719291651453	#N/A
9	FCT417A10	83136647		04/11/21	1738419	4013 WH 50GSMX90MM GEN4 INLINE	28,500	23,004	LB	23.94	04/13/21	06871929	6871929	68719291738419	#N/A
10	FCT417A10	83050489		04/12/21	1738419	4013 WH 50GSMX90MM GEN4 INLINE	28,500	0	LB	23.94	04/14/21	06871929	6871929	68719291738419	#N/A
11	FCT417A10	83281749		04/13/21	1789354	4013 WHITE 50GSMX84MM INLINE	27,000	0	LB	24.57	04/15/21	06871929	6871929	68719291789354	#N/A
12	FCT417A10	83201354		04/14/21	1789354	4013 WHITE 50GSMX84MM INLINE	27,000	0	LB	24.57	04/16/21	06871929	6871929	68719291789354	#N/A
13	FCT417A10	83195097		04/15/21	1789354	4013 WHITE 50GSMX84MM INLINE	27,000	0	LB	24.57	04/17/21	06871929	6871929	68719291789354	#N/A
14	FCT417A10	83195098		04/16/21	1789354	4013 WHITE 50GSMX84MM INLINE	27,000	0	LB	24.57	04/18/21	06871929	6871929	68719291789354	#N/A
15	FCT417A10	83195099		04/17/21	1789354	4013 WHITE 50GSMX84MM INLINE	27,000	0	LB	24.57	04/19/21	06871929	6871929	68719291789354	#N/A
16	FCT417A10	83195100		04/18/21	1789354	4013 WHITE 50GSMX84MM INLINE	27,000	0	LB	24.57	04/20/21	06871929	6871929	68719291789354	#N/A
17	FCT417A10	83201410		04/19/21	1651455	4013 WHT 50GSMX1150MM RES (AV)	38,000	0	LB	23.94	04/23/21	06871929	6871929	68719291651455	#N/A
18	FCT417A10	83201414		04/20/21	1651455	4013 WHT 50GSMX1150MM RES (AV)	38,000	0	LB	23.94	04/24/21	06871929	6871929	68719291651455	#N/A
19	FCT417A10	83249310	A	04/21/21	RESERVED	RESERVED	24	0	EA	0	04/22/21			RESERVED	#N/A
20	FCT417A10	83247968	B	04/21/21	RESERVED	RESERVED	12	0	EA	0	04/27/21			RESERVED	#N/A
21	FCT417A10	83201415	C	04/21/21	1651455	4013 WHT 50GSMX1150MM RES (AV)	38,000	0	LB	23.94	04/25/21	06871929	6871929	68719291651455	#N/A
22	FCT417A10	83201423		04/22/21	1651455	4013 WHT 50GSMX1150MM RES (AV)	38,000	0	LB	23.94	04/26/21	06871929	6871929	68719291651455	#N/A
23	FCT417A10	83210162		04/23/21	1738419	4013 WH 50GSMX90MM GEN4 INLINE	28,500	0	LB	23.94	04/25/21	06871929	6871929	68719291738419	#N/A
24	FCT417A10	83139849		04/24/21	1738419	4013 WH 50GSMX90MM GEN4 INLINE	28,500	0	LB	23.94	04/26/21	06871929	6871929	68719291738419	#N/A
25	FCT417A10	83139853		04/25/21	1738419	4013 WH 50GSMX90MM GEN4 INLINE	28,500	0	LB	23.94	04/27/21	06871929	6871929	68719291738419	#N/A
26	FCT417A10	83211680		04/26/21	1738419	4013 WH 50GSMX90MM GEN4 INLINE	14,250	0	LB	11.97	04/28/21	06871929	6871929	68719291738419	#N/A
27	FCT417A10	83201364		04/27/21	1789354	4013 WHITE 50GSMX84MM INLINE	27,000	0	LB	24.57	04/29/21	06871929	6871929	68719291789354	#N/A
28	FCT417A10	83201374		04/28/21	1789354	4013 WHITE 50GSMX84MM INLINE	27,000	0	LB	24.57	04/30/21	06871929	6871929	68719291789354	#N/A
29	FCT417A10	83139856		04/29/21	1738419	4013 WH 50GSMX90MM GEN4 INLINE	28,500	0	LB	23.94	05/01/21	06871929	6871929	68719291738419	#N/A
30	FCT417A10	83139861		04/30/21	1738419	4013 WH 50GSMX90MM GEN4 INLINE	28,500	0	LB	23.94	05/02/21	06871929	6871929	68719291738419	#N/A
31	FCT417A28	83222271		04/07/21	RESERVED	RESERVED	288	0	EA	288	04/19/21			RESERVED	#N/A
32	FCT417A28	83110623		04/19/21	1636190	SWIFF SCRUB STRP GRN 70gX676MM	40,240	0	LB	67.2	04/22/21	07453020	7453020	74530201636190	#N/A
33	FCT417A28	83235344		04/25/21	1630793	DH267 WHT 360 25gsmX59.25"	35,000	0	LB	44.8	04/27/21	07511952	7511952	75119521630793	#N/A
34	FCT417A28	83260872		04/26/21	1630793	DH267 WHT 360 25gsmX59.25"	35,000	0	LB	44.8	04/29/21	07524608	7524608	75246081630793	#N/A
35	FCT417A28	83260875		04/28/21	1630793	DH267 WHT 360 25gsmX59.25"	30,000	0	LB	38.4	05/01/21	07524678	7524678	75246781630793	#N/A
36	FCT417A28	83226179		04/30/21	1636041	EASIFLEX 2001 CLR 120 3.8X61.5	5,689	0	LB	10.92	05/02/21	07510259	7510259	75102591636041	#N/A
37	FCT417A30	83247018	A	04/06/21	1843713	51' LDPE LAM 2.00 OSY SBPP	914	914	LM	0.35	04/06/21			1843713	#N/A
38	FCT417A30	83217931		04/11/21	1636246	DH268 WHT 71 32gsmX61" TFX MR	18,000	15,653	LB	23.22	04/12/21	07514381	7514381	75143811636246	#N/A
39	FCT417A30	83217935		04/12/21	1636246	DH268 WHT 71 32gsmX61" TFX MR	9,000	0	LB	11.61	04/13/21	07514381	7514381	75143811636246	#N/A
40	FCT417A30	83164541	A	04/13/21	1648587	BL .001X72" LAM w CA-30 to 35"	10,816	0	LM	3.73	04/13/21	07480502	7480502	74805021648587	#N/A
41	FCT417A30	83142584	B	04/13/21	1636068	2500C BL 63 .005X12" . NO A/S	805	0	LB	1.32	04/14/21	07469424	7469424	74694241636068	#N/A
42	FCT417A30	83142582	C	04/13/21	1636066	2500C BL 63 .005X18" . NO A/S	2,415	0	LB	3.79	04/14/21	07469422	7469422	74694221636066	#N/A
AUKYSCHEX

Cell Formulas
Range	Formula
N2:N42	N2	=RIGHT(M2,7)
O2:O42	O2	=CONCATENATE(N2,F2)
P2:P42	P2	=VLOOKUP($O2,AUKYSCHEX1!$B$2:$E$1001,4,FALSE)

Schedules Template.xlsx
	A	B	C	D	E	F
1	Order Type	Order No	Item No.	Ship Date	Prod Type	SO + Item
2	SZ	7488301	1629552	4/18/2021	M	74883011629552
3	SZ	7492457	1629552	4/21/2021	M	74924571629552
4	SZ	7495711	1629552	4/26/2021	M	74957111629552
5	SZ	7502913	1629552	5/1/2021	M	75029131629552
6	SZ	7523873	1629552	5/11/2021	M	75238731629552
7	SZ	7536086	1629552	5/17/2021	M	75360861629552
8	SZ	7466927	1629552	4/8/2021	M	74669271629552
9	SZ	7527600	1629642	4/27/2021	M	75276001629642
10	SZ	7527652	1629642	4/29/2021	M	75276521629642
11	SZ	7528858	1629645	5/3/2021	M	75288581629645
12	SZ	7523277	1629645	5/4/2021	M	75232771629645
13	SZ	7528860	1629645	5/5/2021	M	75288601629645
14	SZ	7528861	1629645	5/6/2021	M	75288611629645
15	SZ	7535790	1629645	5/7/2021	M	75357901629645
16	SB	6871929	1629662	12/25/2021	M	68719291629662
17	SB	6871929	1629663	12/25/2021	M	68719291629663
18	SB	6871929	1629680	12/25/2021	M	68719291629680
19	SO	7446309	1629733	4/16/2021	M	74463091629733
20	SO	7446325	1629733	4/20/2021	M	74463251629733
21	SO	7446292	1629733	4/12/2021	M	74462921629733
22	SO	7446368	1629770	5/4/2021	M	74463681629770
23	SO	7446375	1629770	5/5/2021	M	74463751629770
24	SO	7446408	1629770	6/2/2021	M	74464081629770
25	SO	7446388	1629770	6/3/2021	M	74463881629770
26	SB	6871929	1629771	12/25/2021	M	68719291629771
27	SB	6871929	1629774	12/25/2021	M	68719291629774
28	SO	7515313	1629787	5/21/2021	M	75153131629787
29	SO	7515315	1629787	6/8/2021	M	75153151629787
30	SO	7515317	1629787	6/22/2021	M	75153171629787
31	SO	7454221	1629787	4/24/2021	M	74542211629787
32	SO	7454224	1629787	5/4/2021	M	74542241629787
33	SO	7515311	1629787	5/18/2021	M	75153111629787
34	SO	7454261	1629787	4/9/2021	M	74542611629787
35	SO	7465456	1629815	4/26/2021	M	74654561629815
36	ST	7497753	1629858	5/22/2021	M	74977531629858
37	SO	7446266	1629885	5/6/2021	M	74462661629885
38	SO	7446273	1629885	5/25/2021	M	74462731629885
39	SO	7479228	1630575	4/14/2021	M	74792281630575
40	SO	7479241	1630575	5/5/2021	M	74792411630575
AUKYSCHEX1

Cell Formulas
Range	Formula
F2:F40	F2	=CONCATENATE(B2,C2)

 

[RoryA]

Your formula is looking for the value in O2 in column B on the other sheet. Since it isn't there (it's in F) you get no match. I think you want (based on your sample tables):

=INDEX(AUKYSCHEX1!$D$2:$D$1001,MATCH(($O2,AUKYSCHEX1!$F$2:$F$1001,0))

 

[Fluff]

Hi & welcome to MrExcel.
How about

=INDEX(AUKYSCHEX1!$E$2:$E$1001,MATCH(O2,AUKYSCHEX1!$F$2:$F$1001,0))

 

[JFray]

RoryA Thank You SO Much! Using the Index & Match worked. Spot checking through my data and have verified the dates to be correct. Thank You!!!!!!! Have a Wonderful Day!

 

[RoryA]

Glad we could help.

(Almost 7 years to make your first post might be a record! )