Copy from one sheet to another baed on an IF AND OR

[swingline]

I'm not sure if what I'm asking is possible to accomplish with excel. But what I would like to do is on sheet one copy list names that meet specific criteria based on IF AND OR of contents of columns next to their names.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]M[/TD]
[TD]YES[/TD]
[/TR]
[TR]
[TD]jim[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD]jeff[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD]bill[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]n[/TD]
[/TR]
[TR]
[TD]sam[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD]doug[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]y[/TD]
[/TR]
</tbody>[/TABLE]

So the idea is that the columns to the right of the names are in order of priority between Y, N, M values from the name column would be copied onto another sheet, and if the YES column has an "n" that row can be ignored. Below would be the expected results, the name list is dynamic, but the columns and their order of priority are static.


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]y[/TD]
[TD]n[/TD]
[TD]m[/TD]
[/TR]
[TR]
[TD]jim[/TD]
[TD]sam[/TD]
[TD]doug[/TD]
[/TR]
[TR]
[TD]jeff[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

You dont want jim appearing in column m before doug even though he has 1 in column M and a y in Yes?

Shame, otherwise this would work.

With jim in A2
in F2
=IFERROR(INDEX($A$2:$A$6,AGGREGATE(15,6,ROW($A$2:$A$6)/((B$2:B$6=1)*($E$2:$E$6="y")),ROWS(A$2:A2))-(2-1),1),"")
copy down and across to H6

Only difference from your output is in the m column jim appears before doug