Lookup value within a date range
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Lookup value within a date range

  1. #1
    New Member
    Join Date
    Nov 2011
    Location
    Brisbane, Australia
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Lookup value within a date range

     
    hi guys,

    I need some help with a formula, I have a value 8319 and date 02-Feb-18, I would like to the formula to return the result of column D, if it can find a match of Column A and also within the date range of Column B and C......
    # From Date End Date Result
    8319 1-May-17 31-Dec-17 A
    8319 1-Jan-18 10-May-18 B
    8319 15-May-18 30-May-18 T

    thanks in advance.
    Cheers,
    Joe

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    82,318
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Lookup value within a date range

    The outcome you expect for 8319 and 02-Feb-18 is B, right?
    Assuming too much and qualifying too much are two faces of the same problem.

  3. #3
    New Member
    Join Date
    Nov 2011
    Location
    Brisbane, Australia
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup value within a date range

    hi Aladin, that's correct. the outcome I expect is B

    Quote Originally Posted by Aladin Akyurek View Post
    The outcome you expect for 8319 and 02-Feb-18 is B, right?

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    82,318
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Lookup value within a date range

    Quote Originally Posted by joelui View Post
    hi Aladin, that's correct. the outcome I expect is B
    # From Date End Date Result 8319 2-Feb-18 B
    8319 1-May-17 31-Dec-17 A
    8319 1-Jan-18 10-May-18 B
    8319 15-May-18 30-May-18 T

    F1: 8319

    G1: 2-feb-18

    In H1 control+shift+enter, not just enter:

    =INDEX($D$2:$D$4,MATCH(F1,IF(G1>=B2:B4,IF(G1<=C2:C4,A2:A4)),0))
    Assuming too much and qualifying too much are two faces of the same problem.

  5. #5
    New Member
    Join Date
    Nov 2011
    Location
    Brisbane, Australia
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup value within a date range

    OMG, you are brilliant! thank you so much!

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    82,318
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Lookup value within a date range

      
    Quote Originally Posted by joelui View Post
    OMG, you are brilliant! thank you so much!
    Glad to help.
    Assuming too much and qualifying too much are two faces of the same problem.

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
  •  

 

 
DMCA.com