Help with Excel formula please

Copper Contributor

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: 



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?


SimoneClarePeterJanePM 1PM2
YY  Simone 
Y Y Simone 
Y  YSimone 
 YY Clare 
 Y  Clare 


Thank you guys.



2 Replies


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.





As variant for





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