Help with Excel formula please

[DianaT]

Hi all,

I have a dataset that looks like the following and I'm trying to eliminate the blanks and consolidate the staff name into Column PM1 and PM2.

For PM 1 I use this formula: 

=IF(A2<>"","Simone",IF(B2<>"","Clare",IF(C2<>"","Peter",IF(D2<>"","Jane",""))))

But I am at a loss with PM 2. The logic is similar but PM2 cannot be equal to PM1. So for the first row is PM2 should be Clare. Is there a way to formulate the row so if result =PM1 move on down to the next name?

Simone	Clare	Peter	Jane	PM 1	PM2
Y	Y			Simone
Y		Y		Simone
Y			Y	Simone
	Y	Y		Clare
		Y	Y	Peter
	Y			Clare
			Y	Jane

Thank you guys.

Diana

[#2]

@DianaT 

Please consider the solution in the the attached workbook. Rather than using "Y"-characters in different positions, I find it so much easier to work with numbers 1 and 2.

By the way, I introduced a named range "Staff". You may replace it with "A1:D1" if you prefer.

[#3]

@DianaT 

As variant for

PM1

=INDEX($A$1:$D$1,LARGE(($A2:$D2="Y")*COLUMN($A$2:$D$2),IF(COUNTIFS($A2:$D2,"Y")>1,2,1)))

PM2

=IF(COUNTIFS($A2:$D2,"Y")=1,"",   INDEX($A$1:$D$1,LARGE(($A2:$D2="Y")*COLUMN($A$2:$D$2),1)))