+ Reply to Thread
Results 1 to 6 of 6

Nth instance, multiple criteria

  1. #1
    Registered User
    Join Date
    06-23-2022
    Location
    iowa, usa
    MS-Off Ver
    365
    Posts
    3

    Nth instance, multiple criteria

    team score TC.xlsm - Excel-000582.jpg

    im looking to find the Nth instance based on 2 criteria. so in the attached image, if i wanted the reason for the second instance of dana that has an x in the "Include" column, i should get the result "FILLER". what formula would i use for this?

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    2,576

    Re: Nth instance, multiple criteria

    Hi,
    maybe something like that:

    =INDEX($C$1:$C$10,AGGREGATE(15,6,ROW($A$1:$A$10)/(ISNUMBER(SEARCH("DANA",$B$1:$B$10)*(A1:A10="X"))),2))
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    06-23-2022
    Location
    iowa, usa
    MS-Off Ver
    365
    Posts
    3

    Re: Nth instance, multiple criteria

    HEY! i think that is what im looking for. not the way i was trying at all. thank you!

  4. #4
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    2,576

    Re: Nth instance, multiple criteria

    I corrected my solution, this would be more accurate and cover all cases:

    =INDEX($C$1:$C$10,AGGREGATE(15,6,ROW($A$1:$A$10)/(((ISNUMBER(SEARCH("DANA",$B$1:$B$10))*($A$1:$A$10="X")))),2))

    (the former counted also cases that didnt have "x" in A) .
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-23-2022
    Location
    iowa, usa
    MS-Off Ver
    365
    Posts
    3

    Re: Nth instance, multiple criteria

    HA, YOUR AWESOME. i came back to this thread to tell you that when i started to work this into my sheet, it was still pulling the instance without an X although seems you caught it before me. Thanks again!

  6. #6
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    2,576

    Re: Nth instance, multiple criteria

    HAHA you're welcome. sorry for not catching this sooner, the place of the parentheses is significant and makes the whole difference!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Count Based on First Instance and Criteria
    By Barieq in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-06-2021, 12:05 PM
  2. [SOLVED] Lookup first instance W/Criteria
    By onp in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-28-2021, 07:26 AM
  3. First instance of a time, after a criteria
    By HamerTime7119 in forum Excel General
    Replies: 2
    Last Post: 03-24-2021, 08:26 AM
  4. Pull each instance of a Set Criteria/Value in a coulmn
    By Cbirchfi in forum Excel General
    Replies: 3
    Last Post: 10-12-2016, 10:13 AM
  5. Sum the first Instance of a duplicate across two criteria
    By nas783 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-30-2013, 10:35 PM
  6. Replies: 2
    Last Post: 09-16-2011, 07:45 AM
  7. [SOLVED] Identifying First Instance of a Value Given a Criteria
    By carl in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-10-2006, 02:30 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1