+ Reply to Thread
Results 1 to 11 of 11

use of INDEX match

  1. 10-14-2017, 02:18 PM #1
    LBUG
    LBUG is offline
    Registered User
    Join Date
    10-14-2017
    Location
    Guatemala
    MS-Off Ver
    2016
    Posts
    9

    Exclamation use of INDEX match

    Hello everyone,

    I need some serious help. I would like help on a formula that allows me to match a persons name, the date and the time of a case that was created with the a persons name, the date of a call and in between the start time and end time of a call.

    for example: the names below are the persons that created a case within a call they took. i am trying to get the specific case id
    into the call where it was created (B),

    A: case ids
    Name Date Case created Case ID
    Bill sanders 10/12/2017 5:55 PM 125366
    james Mark 10/13/2017 8:27 AM 125368
    John Smith 10/14/2017 10:35 AM 125369

    B: calls where the case ID was created
    Name Date start end Case ID
    Bill sanders 10/12/2017 4:10 PM 6:20 PM
    james Mark 10/13/2017 8:25 AM 9:22 AM
    John Smith 10/14/2017 10:23 AM 11:21 AM


    ideally, what I am looking for is, for the CASE ID for Bill Sanders to show up where the case created time falls between the start and end time of the call.

    I apologize if I am not explaining it clearly, but its a bit complicated for me.

    the formula I am using, which is not working is below:

    i have gotten as far as matching the name and date with the case ID, but the difficulty i am having is matching those two with the time the case was created within the start and end time of the call.

    this array: =INDEX($D$2:$D$4,MATCH(A2&B2,$F$2:$F$4&$G$2:$G$4,0) gets me the case ID by looking at the name and date
    this array: =MATCH(C2,IF($H$2:$H$4<=C2,IF($I$2:$I$4>=C2,0),0 gets me the case ID by looking only at the time the case was created within the start and end time.

    i am looking for a way to mix the two into one formula so it can match the name, date and time into the name, date and start and end time.
    Reply With QuoteRegister To Reply
  2. 10-14-2017, 03:41 PM #2
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: use of INDEX match

    Hi LBUG- I can't tell if your Dates and times are in one column or two. A sample workbook would really help us solve your problem quickly and accurately.
    Remove any sensitive or extraneous info, we just need some “realistic” data for testing purposes.
    Please include examples of any variations the code or formula must address.
    Also, PLEASE simulate some results to demonstrate what you want.

    To attach a workbook:
    Click Edit Post (or just start a new reply.)
    Click Go Advanced
    Scroll down to Manage Attachments and click.
    Now just Browse for your file, then click Upload. Simple!
    Last edited by leelnich; 10-14-2017 at 03:44 PM.
    Clicking the Add Reputation star below helpful posts is a great way to show your appreciation.
    Please mark your threads as SOLVED upon conclusion (Thread Tools above Post # 1). - Lee
    Reply With QuoteRegister To Reply
  3. 10-14-2017, 04:12 PM #3
    LBUG
    LBUG is offline
    Registered User
    Join Date
    10-14-2017
    Location
    Guatemala
    MS-Off Ver
    2016
    Posts
    9

    Re: use of INDEX match

    sure thing.

    attached is the file I am working on.

    i appreciate your help.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 10-14-2017, 05:01 PM #4
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: use of INDEX match

    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    1
    Name
    Call Dur
    Date
    Start
    End
    Case No
    Name
    Date
    Created
    Case No
    2
    		 Kandy Oliveros
    0:35:24
    9/5/2017
    5:00 AM
    5:35 AM
    		 01085315 Kandy Oliveros
    9/5/2017
    5:04 AM
    		 01085315
    3
    		 Diego Mejia
    0:01:38
    9/1/2017
    6:00 AM
    6:01 AM
    #N/A
    		 Edwin Oliveros
    9/5/2017
    5:15 AM
    		 01085348
    4
    		 Andres Aroldo
    0:01:49
    9/1/2017
    6:01 AM
    6:02 AM
    #N/A
    		 Jude Nulinson
    9/5/2017
    5:26 AM
    		 01085364
    5
    		 Roberto Castro
    0:35:24
    9/1/2017
    6:01 AM
    6:36 AM
    #N/A
    		 Andres Aroldo
    9/5/2017
    5:34 AM
    		 01085378
    6
    		 Kandy Oliveros
    2:52:50
    9/1/2017
    6:01 AM
    8:53 AM
    		 01081548 Greg Rodas
    9/5/2017
    5:40 AM
    		 01085395
    7
    		 Pedro Garcia
    0:06:44
    9/1/2017
    6:01 AM
    6:07 AM
    #N/A
    		 Lourdes Acosta
    9/5/2017
    6:03 AM
    		 01085444
    8
    		 ada Dregonov
    0:03:18
    9/1/2017
    6:02 AM
    6:05 AM
    #N/A
    		 ada Dregonov
    9/5/2017
    6:10 AM
    		 01085466


    The formula in F2, array-entered and copied down, is

    =INDEX($K$2:$K$3677, MATCH(1, (A2 = $H$2:$H$3677) * (C2 = $I$2:$I$3677) * (D2 <= $J$2:$J$3677) * (E2 >= $J$2:$J$3677), 0))
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  5. 10-14-2017, 05:04 PM #5
    gfranco
    gfranco is offline
    Registered User
    Join Date
    10-12-2017
    Location
    Brazil
    MS-Off Ver
    2016
    Posts
    59

    Re: use of INDEX match

    Good afternoon LBUG.
    I used an aggregate function to return the values as per their description.
    The match function has been written in such a way that it will return the line number on which the 4 criteria are met:
    Name, date, and the "Case created" field should be greater than or equal to the Start time field and (at the same time) less than or equal to the End time field.
    In case of not fulfilling all the criteria a "Blank" message will be returned.
    I have noticed that there are many occurrences that are not returning value.
    Take a look.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 10-14-2017, 05:05 PM #6
    LBUG
    LBUG is offline
    Registered User
    Join Date
    10-14-2017
    Location
    Guatemala
    MS-Off Ver
    2016
    Posts
    9

    Re: use of INDEX match

    YOU ARE AWESOME!

    thank both so much!

    Gfranco & Shg, you guys rock!
    Last edited by LBUG; 10-14-2017 at 05:07 PM.
    Reply With QuoteRegister To Reply
  7. 10-14-2017, 05:17 PM #7
    LBUG
    LBUG is offline
    Registered User
    Join Date
    10-14-2017
    Location
    Guatemala
    MS-Off Ver
    2016
    Posts
    9

    Re: use of INDEX match

    thanks gfranco, the "Blank" are correct, since these are interactions, and for each interaction there doesnt need to be a case created, so the fact that there are many blanks is because the case didnt need to be created.

    thanks for your help.

    LBUG
    Reply With QuoteRegister To Reply
  8. 10-14-2017, 05:32 PM #8
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: use of INDEX match

    I think this works, but some cases are missing. Gotta go, I'll try to figure it out later.
    ARRAY FORMULA -*Always press CTRL+SHIFT+ENTER to confirm entry after pasting or editing an ARRAY FORMULA in the Formula Bar.
    When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by leelnich; 10-14-2017 at 05:40 PM.
    Reply With QuoteRegister To Reply
  9. 10-14-2017, 09:12 PM #9
    gfranco
    gfranco is offline
    Registered User
    Join Date
    10-12-2017
    Location
    Brazil
    MS-Off Ver
    2016
    Posts
    59

    Re: use of INDEX match

    OK.
    I am happy to have helped.
    Good luck.
    Reply With QuoteRegister To Reply
  10. 10-15-2017, 03:50 AM #10
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,379

    Re: use of INDEX match

    If this issue is resolved, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  11. 10-15-2017, 08:26 AM #11
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: use of INDEX match

    Actually, the solutions in posts #4, #5, and #8 all share 2 flaws. Any given responder often has overlapping calls, where one begins while others are in progress. In 574 such instances, the SAME case number is assigned to multiple calls. A further 1923 cases are NOT ASSIGNED at all because their recorded creation times fall just AFTER a call ends, but before another begins. Only 1179 of 3676 cases are associated with exactly ONE call. Are such results acceptable?

    I tried using a worksheet formula to fix the overlap (after sorting both data sets by date and time): 
    Please Login or Register  to view this content.
    ... but Excel proved unable to calculate it for 11,000 rows. Maybe someone can find a more efficient formula, but IMO a VBA solution might be better.

    The attached workbook just demonstrates my methods for counting the flaws discussed above. It does NOT offer a new solution.
    Attached Files Attached Files
    Last edited by leelnich; 10-15-2017 at 12:45 PM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] INDEX+MATCH instead of VLOOKUP+MATCH, why is INDEX a better choice and how to re-write?
    By Renejorgensen in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-23-2016, 10:54 AM
  2. [SOLVED] Index / Match - match 3 input values and return the results from the index
    By t83357 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-08-2016, 07:34 PM
  3. [SOLVED] Match-Index in stead of Index-Match lookup Array among Arrays
    By Numnum in forum Excel General
    Replies: 2
    Last Post: 10-15-2015, 02:08 PM
  4. INDEX MATCH MATCH/OFFSET MATCH MATCH with named ranges
    By Andrew-Mark in forum Excel General
    Replies: 3
    Last Post: 02-27-2015, 10:56 PM
  5. Problems with multi-criteria Index/Match. LOOKUP works. Index/Match, SUMPRODUCT,ARRAY fail
    By sfike in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  6. Index, match, dynamic and connected without sorting helper column non-adjacent index match
    By Underling in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-08-2013, 10:29 PM
  7. [SOLVED] Concatenated INDEX MATCH formula that won't give error that can index match 2-4 strings
    By bkwins in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-02-2013, 01:31 AM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1