Vlookup between 2 dates ?
Results 1 to 8 of 8

Thread: Vlookup between 2 dates ?

  1. #1
    Board Regular
    Join Date
    Nov 2016
    Posts
    600
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Vlookup between 2 dates ?

    Hi,
    Not sure if vlookup is the correct thing to use for this but i have data like below:

    Name StartDate EndDate
    John 12/05/2019 15/05/2019
    Luke 14/05/2019 14/05/2019
    Anna 11/05/2019 16/05/2019
    Alex 14/05/2019 16/05/2019

    Now for example if a have a sheet with dates if row 1

    11/05/2019 12/05/2019 13/05/2019 14/05/2019 15/05/2019 16/05/2019
    Anna John John John John Anna
    Anna Anna Luke Anna Alex
    Anna Alex
    Alex


    Any idea how this could be done (formula / powerquery or vba) ?

    any help appreciated

  2. #2
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,830
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: How can this vlookup be achieved between 2 dates ?

    vlookup will only return the first answer, so if you expect more then that isn't the route to persue, it allows prefers a sorted data list
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  3. #3
    Board Regular
    Join Date
    Nov 2016
    Posts
    600
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How can this vlookup be achieved between 2 dates ?

    Quote Originally Posted by mole999 View Post
    vlookup will only return the first answer, so if you expect more then that isn't the route to persue, it allows prefers a sorted data list
    Ye vlookup isn't the right thing for this, in the 2nd table I'm wanting to output the names for each date.

    I think I maybe need an array or an helper column in the first table

  4. #4
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,679
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How can this vlookup be achieved between 2 dates ?

    expected result?
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  5. #5
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,704
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How can this vlookup be achieved between 2 dates ?

    Hi,

    Given the following exists across range A1:J5:

    Name StartDate EndDate 11/05/2019 12/05/2019 13/05/2019 14/05/2019 15/05/2019 16/05/2019
    John 12/05/2019 15/05/2019 Anna John John John John Anna
    Luke 14/05/2019 14/05/2019 Anna Anna Luke Anna Alex
    Anna 11/05/2019 16/05/2019 Anna Alex
    Alex 14/05/2019 16/05/2019 Alex

    Formula in E2 is:

    Code:
    =IFERROR(INDEX($A$2:$A$5,SMALL(IF(E$1>=$B$2:$B$5,IF(E$1<=$C$2:$C$5,ROW($A$2:$A$5)-ROW($A$2)+1)),ROWS(E$2:E2))),"")
    Copied down and across.

    Note: this formula requires array processing, so needs to be committed with CTRL+SHIFT+ENTER.

    Matty

  6. #6
    Board Regular
    Join Date
    Nov 2016
    Posts
    600
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How can this vlookup be achieved between 2 dates ?

    Thankyou @Matty
    This looks correct I will try it when I get on PC

  7. #7
    Board Regular
    Join Date
    Nov 2016
    Posts
    600
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How can this vlookup be achieved between 2 dates ?

    Quote Originally Posted by sandy666 View Post
    expected result?
    Expected output as the 2nd table I posted, from only the dates in row 1, names output below for each in table 1 range

  8. #8
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,679
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How can this vlookup be achieved between 2 dates ?

    Ok, I misunderstood

    never mind

    Have a nice day
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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