Why D3 to D7 return #N/A? Pls. advise.

[felixkk07]

055 - (Status up to 15 Apr 2023) TCLM0xx Contractor's Dimbook Submission (Summary).xlsx
	A	B	C	D
2	Works Order Number	DIM Book Submission Date	MonthYear Info	Label on X-axis
3	TCLM011B000004	2023-4-1	01 April 2023	#N/A
4	TCLM011B000005	2023-4-11	11 April 2023	#N/A
5	TCLM021B000005	2023-3-30	30 March 2023	#N/A
6	TCLM011B000023	2023-4-14	14 April 2023	#N/A
7	TCLM011B000057	2023-4-14	14 April 2023	#N/A
Sourcedata_DB Overview_20230417

Cell Formulas
Range	Formula
C3:C7	C3	=TEXT(B3,"dd")& TEXT(B3," mmmm")& TEXT(B3," yyyy")
D3:D7	D3	=LOOKUP(C3,DATEVALUE(table!$A$2:$B$15),table!$C$2:$C$15)

055 - (Status up to 15 Apr 2023) TCLM0xx Contractor's Dimbook Submission (Summary).xlsx
	A	B	C
1	Start Date	End Date	Label on X-axis
2	16/11/2022	15/12/2022	Mid-Dec 2022
3	16/12/2022	15/1/2023	Mid-Jan 2023
4	16/1/2023	15/2/2023	Mid-Feb 2023
5	16/2/2023	15/3/2023	Mid-Mar 2023
6	16/3/2023	15/4/2023	Mid-Apr 2023
7	16/4/2023	15/5/2023	Mid-May 2023
8	16/5/2023	15/6/2023	Mid-Jun 2023
9	16/6/2023	15/7/2023	Mid-Jul 2023
10	16/7/2023	15/8/2023	Mid-Aug 2023
11	16/8/2023	15/9/2023	Mid-Sep 2023
12	16/9/2023	15/10/2023	Mid-Oct 2023
13	16/10/2023	15/11/2023	Mid-Nov 2023
14	16/11/2023	15/12/2023	Mid-Dec 2023
15	16/12/2023	15/1/2024	Mid-Jan 2024
table

Cell Formulas
Range	Formula
A2	A2	=TEXT("16/11/2022","dd/mm/yyyy")
B2	B2	=TEXT("15/12/2022","dd/mm/yyyy")
A3	A3	=TEXT("16/12/2022","dd/mm/yyyy")
B3	B3	=TEXT("15/1/2023","dd/mm/yyyy")
A4	A4	=TEXT("16/1/2023","dd/mm/yyyy")
B4	B4	=TEXT("15/2/2023","dd/mm/yyyy")
A5	A5	=TEXT("16/2/2023","dd/mm/yyyy")
B5	B5	=TEXT("15/3/2023","dd/mm/yyyy")
A6	A6	=TEXT("16/3/2023","dd/mm/yyyy")
B6	B6	=TEXT("15/4/2023","dd/mm/yyyy")
A7	A7	=TEXT("16/4/2023","dd/mm/yyyy")
B7	B7	=TEXT("15/5/2023","dd/mm/yyyy")
A8	A8	=TEXT("16/5/2023","dd/mm/yyyy")
B8	B8	=TEXT("15/6/2023","dd/mm/yyyy")
A9	A9	=TEXT("16/6/2023","dd/mm/yyyy")
B9	B9	=TEXT("15/7/2023","dd/mm/yyyy")
A10	A10	=TEXT("16/7/2023","dd/mm/yyyy")
B10	B10	=TEXT("15/8/2023","dd/mm/yyyy")
A11	A11	=TEXT("16/8/2023","dd/mm/yyyy")
B11	B11	=TEXT("15/9/2023","dd/mm/yyyy")
A12	A12	=TEXT("16/9/2023","dd/mm/yyyy")
B12	B12	=TEXT("15/10/2023","dd/mm/yyyy")
A13	A13	=TEXT("16/10/2023","dd/mm/yyyy")
B13	B13	=TEXT("15/11/2023","dd/mm/yyyy")
A14	A14	=TEXT("16/11/2023","dd/mm/yyyy")
B14	B14	=TEXT("15/12/2023","dd/mm/yyyy")
A15	A15	=TEXT("16/12/2023","dd/mm/yyyy")
B15	B15	=TEXT("15/1/2024","dd/mm/yyyy")

 

[AhoyNC]

The issue is in your LOOKUP C3 is text and your lookup range in table!$A$2:$B$15 you are changing to dates (numeric) so it can't find a match.
To use LOOKUP you need to change all your dates to actual dates.

 

[Dave Patton]

The Table edited to have real dates not text in Column A.
The Lookup uses Int because the dates seem to include time.

T202304a.xlsm
	A	B	C	D
1	Works Order Number	DIM Book Submission Date	MonthYear Info	Label on X-axis
2	TCLM011B000004	01-Apr-2023		Mid-Apr 2023
3	TCLM011B000005	11-Apr-2023		Mid-Apr 2023
4	TCLM021B000005	30-Mar-2023		Mid-Apr 2023
5	TCLM011B000023	14-Apr-2023		Mid-Apr 2023
6	TCLM011B000057	14-Apr-2023		Mid-Apr 2023
7
5b

Cell Formulas
Range	Formula
D2:D6	D2	=LOOKUP(INT(B2),Table!$A$2:$B$15)

T202304a.xlsm
	A	B
1	Start Date	Label on X-axis
2	16-Nov-2022	Mid-Dec 2022
3	16-Dec-2022	Mid-Jan 2023
4	16-Jan-2023	Mid-Feb 2023
5	16-Feb-2023	Mid-Mar 2023
6	16-Mar-2023	Mid-Apr 2023
7	16-Apr-2023	Mid-May 2023
8	16-May-2023	Mid-Jun 2023
9	16-Jun-2023	Mid-Jul 2023
10	16-Jul-2023	Mid-Aug 2023
11	16-Aug-2023	Mid-Sep 2023
12	16-Sep-2023	Mid-Oct 2023
13	16-Oct-2023	Mid-Nov 2023
14	16-Nov-2023	Mid-Dec 2023
15	16-Dec-2023	Mid-Jan 2024
16
Table