Results 1 to 6 of 6

Thread: Index and matching

  1. #1
    New Member
    Join Date
    Apr 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Index and matching

    i'm trying to return a value that matches one criteria exactly, and the second by greater than or equal to. I created two tables below for reference, what I need to do is be able to match the ID # exactly, then find the Encounter date from the second table that is equal to or greater than the enrollment date in table 1, and return the value. Any help would be appreciated.


    Table 1
    ID Number Enrollment Date
    AR-AB20396* 10/3/2018
    AT-AB37900* 2/22/2019
    BC-AB14407 10/5/2018
    CC-AB14345* 10/5/2018
    CJ-AB14904* 10/3/2018


    Table 2
    ID # ENCOUNTER DATE Value
    AR-AB20396* 9/12/2018 5.5
    AT-AB37900* 2/22/2019 5.3
    BC-AB14407 5/4/2018 8.3
    BC-AB14407 7/31/2018 7.7
    BC-AB14407 10/12/2018 7.7
    BC-AB14407 3/4/2019 8.4
    CC-AB14345* 4/17/2018 7
    CC-AB14345* 7/30/2018 6.8
    CC-AB14345* 8/2/2018 6.8
    CC-AB14345* 12/4/2018 6.8
    CJ-AB14904* 1/17/2018 6.4
    CJ-AB14904* 5/13/2018 7
    CS-AB35454 6/26/2018 6.9

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,353
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Index and matching

    Hi & welcome to MrExcel.
    How about

    Excel 2013/2016
    ABCJKLM
    1ID NumberEnrollment DateID #ENCOUNTER DATEValue
    2AR-AB20396*03/10/2018AR-AB20396*12/09/20185.5
    3AT-AB37900*22/02/20195.3AT-AB37900*22/02/20195.3
    4BC-AB1440705/10/20187.7BC-AB1440704/05/20188.3
    5CC-AB14345*05/10/20186.8BC-AB1440731/07/20187.7
    6CJ-AB14904*03/10/2018BC-AB1440712/10/20187.7
    7BC-AB1440704/03/20198.4
    8CC-AB14345*17/04/20187
    9CC-AB14345*30/07/20186.8
    10CC-AB14345*02/08/20186.8
    11CC-AB14345*04/12/20186.8
    12CJ-AB14904*17/01/20186.4
    13CJ-AB14904*13/05/20187
    14CS-AB3545426/06/20186.9

    List



    Array Formulas
    CellFormula
    C2{=IFERROR(INDEX($M$2:$M$14,MATCH(1,($K$2:$K$14=A2)*($L$2:$L$14>=B2),0)),"")}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Apr 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index and matching

    Thanks for the quick response, your solution is very similar to one I had previous tried. Unfortunately I get the same result that it only returns a value for when the two dates are exact matches.
    Here is my formula
    =IFERROR(INDEX(Sheet2!$Q$3:$Q$10000,MATCH(1,(Sheet2!$A$3:$A$10000=A2)*(Sheet2!$D$3:$D$10000>=B2),0)),"")

    Do you see where I went wrong, I am referencing two different sheets.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,353
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Index and matching

    That looks ok. Did you confirm it with CSE?
    Also are you sure that your dates are real dates, rather than text?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Apr 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index and matching

    It looks like I got something to work, My original table didn't always have a number placed in the value column. Looks like that might have been throwing things off. Even though i don't understand why.
    Thanks for your help.

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,353
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Index and matching

    Glad you got it sorted & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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
  •