+ Reply to Thread
Results 1 to 19 of 19

Search a table and return value based on today's date

  1. #1
    Forum Contributor her.rockstar's Avatar
    Join Date
    01-11-2013
    Location
    Saint John, NB
    MS-Off Ver
    Office 365
    Posts
    194

    Search a table and return value based on today's date

    I have a table with 4 Shift's schedules.

    I would like a cell somewhere else on the spreadsheet to to identify which shift is working today.

    I tried a few hlookups and vlookups, but I think there are too many layers in this for me to comprehend. Anyone want to take a stab at it?

    Book2.xls

  2. #2
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Search a table and return value based on today's date

    This gets the data from the current date.
    Attached Files Attached Files
    Gary's Student

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Search a table and return value based on today's date

    How would you arrange the data for subsequent months? Would this carry on across the columns, or appear below the January data in blocks of a few rows?

    Pete

  4. #4
    Forum Contributor her.rockstar's Avatar
    Join Date
    01-11-2013
    Location
    Saint John, NB
    MS-Off Ver
    Office 365
    Posts
    194

    Re: Search a table and return value based on today's date

    Hey I'm sorry. I realize I didn't give enough information.

    I would like the cell to show the Shift that's currently working the "day" shift... ie, B or D

  5. #5
    Forum Contributor her.rockstar's Avatar
    Join Date
    01-11-2013
    Location
    Saint John, NB
    MS-Off Ver
    Office 365
    Posts
    194

    Re: Search a table and return value based on today's date

    Okay, so, the entire schedule has all the months below it.

    I'll update a bigger piece of the picture.

    Book2.xls

    So, the first step will identify the table for the current month, then it will identify the current day, then the row below it with "D". The final step will identify and list which Shift is working.

    Does that make sense? So today, it should show A Shift as working.

  6. #6
    Forum Contributor her.rockstar's Avatar
    Join Date
    01-11-2013
    Location
    Saint John, NB
    MS-Off Ver
    Office 365
    Posts
    194

    Re: Search a table and return value based on today's date

    All I want to know is which Shift is working today.

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Search a table and return value based on today's date

    You've attached the same workbook as before.

    Pete

  8. #8
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Search a table and return value based on today's date

    =index($a$3:$a$6,match("n",offset($a$1,2,match(day(today()),$a$2:$af$2,0)-1,5),0))
    =index($a$3:$a$6,match("d",offset($a$1,2,match(day(today()),$a$2:$af$2,0)-1,5),0))
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  9. #9
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Search a table and return value based on today's date

    So these will work for your current table:

    Night Shift: =INDEX($A$3:$A$6,MATCH("N",OFFSET(INDIRECT(ADDRESS(3,MATCH(DAY(TODAY()),$B$2:$AF$2,0),1,1)),0,0,4,1),0),1)

    Day Shift: [Substitute "D" for "N" in the above]

    Need a bit more information about where your month tables are stored before I can adapt for that.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  10. #10
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Search a table and return value based on today's date

    Just to be clear, you want to:


    1. look along row #2 to find today's date
    2. look down the column to find the "D" (day shift)
    3. look back to column #1 to find the shift (A/B/C/D)

  11. #11
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Search a table and return value based on today's date

    =index($a$3:$a$6,match("n",offset($a$1,2,match(day(today()),$a$2:$af$2,0)-1,5),0))
    =index($a$3:$a$6,match("d",offset($a$1,2,match(day(today()),$a$2:$af$2,0)-1,5),0))
    both these give d and n shifts ,from that table it returns B for "d" and A for "n" for the 16th

  12. #12
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Search a table and return value based on today's date

    Thanks, Martin.

    Should be: Night Shift:

    =INDEX($A$3:$A$6,MATCH("N",OFFSET(INDIRECT(ADDRESS(3,MATCH(DAY(TODAY()),$B$2:$AF$2,0)+1,1,1)),0,0,4,1),0),1)

    And yours is much simpler and more elegant anyway!

  13. #13
    Forum Contributor her.rockstar's Avatar
    Join Date
    01-11-2013
    Location
    Saint John, NB
    MS-Off Ver
    Office 365
    Posts
    194

    Re: Search a table and return value based on today's date

    Sorry guys, I didn't mean to upload the same book.

    Here's the bigger one.

    Book3.xls

    Thank you everyone for all your help, I really appreciate it.

  14. #14
    Forum Contributor her.rockstar's Avatar
    Join Date
    01-11-2013
    Location
    Saint John, NB
    MS-Off Ver
    Office 365
    Posts
    194

    Re: Search a table and return value based on today's date

    And yes, Jakobshavn, that's exactly what I'm hoping to do.

  15. #15
    Forum Contributor her.rockstar's Avatar
    Join Date
    01-11-2013
    Location
    Saint John, NB
    MS-Off Ver
    Office 365
    Posts
    194

    Re: Search a table and return value based on today's date

    I should clarify, it has to be able to identify the current month as well, in order to know where to look.

  16. #16
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Search a table and return value based on today's date

    =INDEX($A$3:$A$7,MATCH("n",OFFSET($A$1,MATCH(TEXT(TODAY(),"mmm"),$A$2:$A$85,0),MATCH(DAY(TODAY()),$A$3:$AF$3,0)-1,5))) gives shift D for "n" and shift A for "d" may 16

  17. #17
    Forum Contributor her.rockstar's Avatar
    Join Date
    01-11-2013
    Location
    Saint John, NB
    MS-Off Ver
    Office 365
    Posts
    194

    Re: Search a table and return value based on today's date

    Is there any way to know which shift in column 1 is working the D shift though?

  18. #18
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Search a table and return value based on today's date

    correction
    =INDEX($A$3:$A$7,MATCH("D",OFFSET($A$1,MATCH(TEXT(TODAY(),"mmmm"),$A$2:$A$85,0),MATCH(DAY(TODAY()),$A$3:$AF$3,0)-1,5),0))
    =INDEX($A$3:$A$7,MATCH("N",OFFSET($A$1,MATCH(TEXT(TODAY(),"mmmm"),$A$2:$A$85,0),MATCH(DAY(TODAY()),$A$3:$AF$3,0)-1,5),0))
    shows shift A is working the Day? and shift D is working the Night?

  19. #19
    Forum Contributor her.rockstar's Avatar
    Join Date
    01-11-2013
    Location
    Saint John, NB
    MS-Off Ver
    Office 365
    Posts
    194

    Re: Search a table and return value based on today's date

    BINGO! You nailed it.

    Thanks very much for your help. You and everyone!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1