Nth Occurence, Multiple Criteria

[nwillis]

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.

Is anyone able to please help?

Thanks,
Noel

 

[Aladin Akyurek]

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

 

[mikerickson]

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)

 

[nwillis]

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

Hi Aladin,

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]

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