VLookup giving wrong result based upon date

Jlambert42

New Member
Joined
Oct 20, 2016
Messages
6
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
 

Attachments

  • vlookup problem.png
    vlookup problem.png
    37.7 KB · Views: 4

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,903
Office Version
  1. 365
Platform
  1. Windows
The formula is returning the nearest lower date from col AH, which is 30th Aug.
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
can you install xl2bb and dump that as a table rather than image
 

Jlambert42

New Member
Joined
Oct 20, 2016
Messages
6
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

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
you can get the weeks from just a date, you don't need a reference table, I don't think
 

Jlambert42

New Member
Joined
Oct 20, 2016
Messages
6
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

AuditorSunMonTueWedThurFriSat
DateAudit TypeSupervisorTeam LeadAugust-201131
Aug Week 18/3/20205SBrett Davis08/07/20Week 12345678229
Aug Week 18/4/2020LAJanet BlackWeek 291011121314153318/1/2020
Aug Week 18/4/20205SCameron EyestoneAug Week 4161718192021224308/2/20208/3/20208/4/20208/5/20208/6/20208/7/20208/8/2020Aug Week 1
Aug Week 38/17/2020HOPSJohn ErvinAug Week 5232425262728295318/9/20208/10/20208/11/20208/12/20208/13/20208/14/20208/15/2020Aug Week 2
Aug Week 18/3/2020HOPSBrett DavisAug Week 630316308/16/20208/17/20208/18/20208/19/20208/20/20208/21/20208/22/2020Aug Week 3
Aug Week 18/3/2020SWADan Sampson7318/23/20208/24/20208/25/20208/26/20208/27/20208/28/20208/29/2020Aug Week 4
Aug Week 18/5/2020SWAJanet Black18318/30/20208/31/2020Aug Week 5
Aug Week 59/2/2020SWARobert WilsonAugust-20930
Sep Week 510/2/2020HOPSJerry Neale810319/1/20209/2/20209/3/20209/4/20209/5/2020Sep Week 1
Oct Week 310/17/20205SGerald PrairieNo202011309/6/20209/7/20209/8/20209/9/20209/10/20209/11/20209/12/2020Sep Week 2
3112319/13/20209/14/20209/15/20209/16/20209/17/20209/18/20209/19/2020Sep Week 3
8/7/20209/20/20209/21/20209/22/20209/23/20209/24/20209/25/20209/26/2020Sep Week 4
9/27/20209/28/20209/29/20209/30/2020Sep Week 5
08/01/200
08/02/2008/03/2008/04/2008/05/2008/06/2008/07/2008/08/205
08/09/2008/10/2008/11/2008/12/2008/13/2008/14/2008/15/20510/1/202010/2/202010/3/2020Oct Week 1
08/16/2008/17/2008/18/2008/19/2008/20/2008/21/2008/22/20510/4/202010/5/202010/6/202010/7/202010/8/202010/9/202010/10/2020Oct Week 2
08/23/2008/24/2008/25/2008/26/2008/27/2008/28/2008/29/20510/11/202010/12/202010/13/202010/14/202010/15/202010/16/202010/17/2020Oct Week 3
08/30/2008/31/20110/18/202010/19/202010/20/202010/21/202010/22/202010/23/202010/24/2020Oct Week 4
10/25/202010/26/202010/27/202010/28/202010/29/202010/30/202010/31/2020Oct Week 5
September-20Oct Week 112345131
08/07/20Week 16789101112229
Week 213141516171819331
Week 320212223242526430
Week 427282930531
Week 5630
731
1831
September-20930
91031
No20201130
301231
8/7/2020
09/01/2009/02/2009/03/2009/04/2009/05/204
09/06/2009/07/2009/08/2009/09/2009/10/2009/11/2009/12/205
09/13/2009/14/2009/15/2009/16/2009/17/2009/18/2009/19/205
09/20/2009/21/2009/22/2009/23/2009/24/2009/25/2009/26/205
09/27/2009/28/2009/29/2009/30/203
0
October-20Week 1123131
08/07/20Week 245678910229
Week 311121314151617331
Week 418192021222324430
Week 525262728293031531
630
731
1831
October-20930
101031
No20201130
311231
8/7/2020
10/01/2010/02/2010/03/202
10/04/2010/05/2010/06/2010/07/2010/08/2010/09/2010/10/205
10/11/2010/12/2010/13/2010/14/2010/15/2010/16/2010/17/205
10/18/2010/19/2010/20/2010/21/2010/22/2010/23/2010/24/205
10/25/2010/26/2010/27/2010/28/2010/29/2010/30/2010/31/205
0
 

Watch MrExcel Video

Forum statistics

Threads
1,118,809
Messages
5,574,435
Members
412,592
Latest member
moonsugar
Top