C2公式:=IF(INDEX(人员安排表!C:C,SUMPRODUCT((B2=人员安排表!$C$2:$E$100)*ROW($C$2:$E$100)),)=B2,INDEX(人员安排表!D:D,SUMPRODUCT((B2=人员安排表!$C$2:$E$100)*ROW($C$2:$E$100)),),INDEX(人员安排表!C:C,SUMPRODUCT((B2=人员安排表!$C$2:$E$100)*ROW($C$2:$E$100)),))&"" D2公式:=IF(INDEX(人员安排表!E:E,SUMPRODUCT((B2=人员安排表!$C$2:$E$100)*ROW($C$2:$E$100)),)=B2,INDEX(人员安排表!D:D,SUMPRODUCT((B2=人员安排表!$C$2:$E$100)*ROW($C$2:$E$100)),),INDEX(人员安排表!E:E,SUMPRODUCT((B2=人员安排表!$C$2:$E$100)*ROW($C$2:$E$100)),))&"" 虽然长了点,但不是数组的,运算较快一点 |