# Nth instance, multiple criteria

1. ## 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. ## 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))

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. ## 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) .

5. ## 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. ## 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!

##### Users Browsing this Thread

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

#### 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