# Nth Occurence, Multiple Criteria

#### nwillis

##### New Member
Hi there,

I have a question i've been stuck with for a couple of days now. My data is set up as below:

 A B C D E 1 Ben Simon Rob Mike 2 1 Dec 16 Yes No No Yes 3 2 Dec 16 Yes No Yes No 4 3 Dec 16 No No No No 5 4 Dec 16 Yes Yes No No

<tbody>
</tbody>

Apologies i don't know how to paste a picutre into the box, but basically cell A1 is blank, B1 is Ben, A2 is 1 Dec16, B2 is yes etc etc

I'm trying to use a formula to find whose name contains the 2nd occurrence of 'yes' on the date 1 Dec 16. I'm using:

=INDEX(\$B\$1:\$E\$1,MATCH("YES",INDEX(\$B\$2:\$E\$2,MATCH("Date",\$A\$2:\$A\$5,0),0),0))

This returns 'Ben', but what i want to do is return 'Mike' as that is the 2nd occurrence of 'Yes' on that date.

Thanks,
Noel

### Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

##### MrExcel MVP
Instead of discussing a non-working formula, care to specify the outcome you expect for the sample you posted?

#### mikerickson

##### MrExcel MVP
Try the CSE formula, where H1 holds the date in question

=INDEX(\$A\$1:\$E\$1, 1, SMALL(IF(INDEX(\$A\$2:\$E\$5, MATCH(H1, \$A\$2:\$A\$5, 0),0)="Yes", COLUMN(\$A\$1:\$E\$1)), 2))

Entered with Ctrl-Shift-Enter (Cmd+Return for Mac)

Last edited:

#### nwillis

##### New Member
Instead of discussing a non-working formula, care to specify the outcome you expect for the sample you posted?

Sorry I might not have made it clear enough but I actually said the outcome i needed in there twice, i.e. to find whose name contains the 2nd occurrence of the word 'Yes' on 1 Dec 16.

Cheers

#### nwillis

##### New Member
Try the CSE formula, where H1 holds the date in question

=INDEX(\$A\$1:\$E\$1, 1, SMALL(IF(INDEX(\$A\$2:\$E\$5, MATCH(H1, \$A\$2:\$A\$5, 0),0)="Yes", COLUMN(\$A\$1:\$E\$1)), 2))

Entered with Ctrl-Shift-Enter (Cmd+Return for Mac)

Brilliant thanks Mike this has absolutely nailed it! I was trying to use the small function but to no avail, many thanks for your help.

Noel

Replies
4
Views
352
Replies
4
Views
290
Replies
7
Views
1K
Replies
4
Views
721
Replies
15
Views
905

1,190,836
Messages
5,983,172
Members
439,825
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back