Results 1 to 8 of 8

Thread: Vlookup between 2 dates ?

  1. #1
    Board Regular
    Join Date
    Nov 2016
    Posts
    607
    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,856
    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
    607
    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,863
    Post Thanks / Like
    Mentioned
    32 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
    I don't use vba in any form!

  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
    607
    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
    607
    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,863
    Post Thanks / Like
    Mentioned
    32 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
    I don't use vba in any form!

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
  •