VLookup giving wrong result based upon date

[Jlambert42]

Hello all and thank you for taking a moment to read over this.

I have a spreadsheet where the date is entered in column B, a vlookup formula in column A then looks at columns AA to AG to return what week that date falls on in column AH. The yellow highlighted cells in A and B columns are the ones returning the errors, but the formula is the same as the other cells in column A. For the life of me, I cannot see why putting in 9/4/2020 is returning Aug Week 5 and not Sep Week 1. I've tried an index/match formula, but it returns a NA, but I may have figured that wrong.
Current vlookup formula - =IF(B10="","",VLOOKUP(B10,$AA$4:$AH$30,8,1))
if I change the 1 to 0, I get NA error.

I've pretty much burned out what braincells I have that are actually working on a Friday, so if anyone has any thoughts where I went sideways on this, I'd appreciate the feedback.
Thank you and have a great weekend!
Jon

 

[Fluff]

The formula is returning the nearest lower date from col AH, which is 30th Aug.

 

[mole999]

can you install xl2bb and dump that as a table rather than image

 

[Jlambert42]

Unfortunately, at this time, no. Not long after posting this, excel decided it was time for a rest and shut off on me. I hadn't saved for a while and I lost what I had entered into the worksheet for most of today. I'll have to try to update with the requested information after I rebuild the sheet.

I should have stayed at home in bed today.
Jon

 

[mole999]

you can get the weeks from just a date, you don't need a reference table, I don't think

 

[Jlambert42]

can you install xl2bb and dump that as a table rather than image

Hello again. I rebuilt it as close to what I had it when I started this thread. Here is the table

			Auditor											Sun	Mon	Tue	Wed	Thur	Fri	Sat
	Date	Audit Type	Supervisor	Team Lead								August-20								1		1	31
Aug Week 1	8/3/2020	5S	Brett Davis									08/07/20	Week 1	2	3	4	5	6	7	8		2	29
Aug Week 1	8/4/2020	LA	Janet Black										Week 2	9	10	11	12	13	14	15		3	31									8/1/2020
Aug Week 1	8/4/2020	5S		Cameron Eyestone									Aug Week 4	16	17	18	19	20	21	22		4	30			8/2/2020	8/3/2020	8/4/2020	8/5/2020	8/6/2020	8/7/2020	8/8/2020	Aug Week 1
Aug Week 3	8/17/2020	HOPS	John Ervin										Aug Week 5	23	24	25	26	27	28	29		5	31			8/9/2020	8/10/2020	8/11/2020	8/12/2020	8/13/2020	8/14/2020	8/15/2020	Aug Week 2
Aug Week 1	8/3/2020	HOPS	Brett Davis										Aug Week 6	30	31							6	30			8/16/2020	8/17/2020	8/18/2020	8/19/2020	8/20/2020	8/21/2020	8/22/2020	Aug Week 3
Aug Week 1	8/3/2020	SWA		Dan Sampson																		7	31			8/23/2020	8/24/2020	8/25/2020	8/26/2020	8/27/2020	8/28/2020	8/29/2020	Aug Week 4
Aug Week 1	8/5/2020	SWA	Janet Black																	1		8	31			8/30/2020	8/31/2020						Aug Week 5
Aug Week 5	9/2/2020	SWA	Robert Wilson									August-20										9	30
Sep Week 5	10/2/2020	HOPS		Jerry Neale																8		10	31					9/1/2020	9/2/2020	9/3/2020	9/4/2020	9/5/2020	Sep Week 1
Oct Week 3	10/17/2020	5S		Gerald Prairie								No								2020		11	30			9/6/2020	9/7/2020	9/8/2020	9/9/2020	9/10/2020	9/11/2020	9/12/2020	Sep Week 2
																				31		12	31			9/13/2020	9/14/2020	9/15/2020	9/16/2020	9/17/2020	9/18/2020	9/19/2020	Sep Week 3
														8/7/2020												9/20/2020	9/21/2020	9/22/2020	9/23/2020	9/24/2020	9/25/2020	9/26/2020	Sep Week 4
																										9/27/2020	9/28/2020	9/29/2020	9/30/2020				Sep Week 5
																				08/01/20	0
														08/02/20	08/03/20	08/04/20	08/05/20	08/06/20	08/07/20	08/08/20	5
														08/09/20	08/10/20	08/11/20	08/12/20	08/13/20	08/14/20	08/15/20	5									10/1/2020	10/2/2020	10/3/2020	Oct Week 1
														08/16/20	08/17/20	08/18/20	08/19/20	08/20/20	08/21/20	08/22/20	5					10/4/2020	10/5/2020	10/6/2020	10/7/2020	10/8/2020	10/9/2020	10/10/2020	Oct Week 2
														08/23/20	08/24/20	08/25/20	08/26/20	08/27/20	08/28/20	08/29/20	5					10/11/2020	10/12/2020	10/13/2020	10/14/2020	10/15/2020	10/16/2020	10/17/2020	Oct Week 3
														08/30/20	08/31/20						1					10/18/2020	10/19/2020	10/20/2020	10/21/2020	10/22/2020	10/23/2020	10/24/2020	Oct Week 4
																										10/25/2020	10/26/2020	10/27/2020	10/28/2020	10/29/2020	10/30/2020	10/31/2020	Oct Week 5
												September-20	Oct Week 1			1	2	3	4	5		1	31
												08/07/20	Week 1	6	7	8	9	10	11	12		2	29
													Week 2	13	14	15	16	17	18	19		3	31
													Week 3	20	21	22	23	24	25	26		4	30
													Week 4	27	28	29	30					5	31
													Week 5									6	30
																						7	31
																1						8	31
												September-20										9	30
																				9		10	31
												No								2020		11	30
																				30		12	31
														8/7/2020
																09/01/20	09/02/20	09/03/20	09/04/20	09/05/20	4
														09/06/20	09/07/20	09/08/20	09/09/20	09/10/20	09/11/20	09/12/20	5
														09/13/20	09/14/20	09/15/20	09/16/20	09/17/20	09/18/20	09/19/20	5
														09/20/20	09/21/20	09/22/20	09/23/20	09/24/20	09/25/20	09/26/20	5
														09/27/20	09/28/20	09/29/20	09/30/20				3
																					0
												October-20	Week 1					1	2	3		1	31
												08/07/20	Week 2	4	5	6	7	8	9	10		2	29
													Week 3	11	12	13	14	15	16	17		3	31
													Week 4	18	19	20	21	22	23	24		4	30
													Week 5	25	26	27	28	29	30	31		5	31
																						6	30
																						7	31
																		1				8	31
												October-20										9	30
																				10		10	31
												No								2020		11	30
																				31		12	31
														8/7/2020
																		10/01/20	10/02/20	10/03/20	2
														10/04/20	10/05/20	10/06/20	10/07/20	10/08/20	10/09/20	10/10/20	5
														10/11/20	10/12/20	10/13/20	10/14/20	10/15/20	10/16/20	10/17/20	5
														10/18/20	10/19/20	10/20/20	10/21/20	10/22/20	10/23/20	10/24/20	5
														10/25/20	10/26/20	10/27/20	10/28/20	10/29/20	10/30/20	10/31/20	5
																					0